Automated data validation

[phil_gauge]

Hi Everyone

I am trying to make a drop down list automatically update based on a set cell value.

what I have is the following:
> 30 cells with dropdown lists
> work schedule of fortnightly, monthly, quarterly and annually
> 10 different sites
> Dependent of the schedule being completed determines what sites are visited (so which drop down cell will obtain a value) and the value selected from the dropdown cells.

To try and make this task easier (reduce time to select data), in W5, I have added a drop down menu that has the different possible work schedules fortnightly, monthly, quarterly and annually and my goal is to have all the other cells automatically display a set value based on the criteria in W5

What I have tried is the following:
> in my dropdown list, the last option in each list I have added a formula (example: =IF(Fieldsheet!W5="Fortnightly","Fortnightly",IF(Fieldsheet!W5="Monthly","Monthly",IF(Fieldsheet!W5="Quarterly","Quarterly",IF(Fieldsheet!W5="Annually","Annually","")))) and another one is =IF(Fieldsheet!W5="Monthly","Monthly",IF(Fieldsheet!W5="Quarterly","Quarterly",IF(Fieldsheet!W5="Annually","Quarterly",""))) ), when I select the bottom value in the dropdown list, however when selected, if data is picked in W5 or W5 changes the dropdown list unfortunately doesn't.
> I've added individual formula to each cell that the drop down list is in so that it changes based on W5 value, this works great, if W5 is blank, everything is blank, and the correct data is shown in the correct cells based on W5. However if the user changes the cells value to another option in the drop down list (as different uncommon options can be selected), the formula is removed and I cannot lock the cell to protect the formula as then the user cannot access the drop down menu.

Is there a way around this?

Thanks in advance.

to give you an idea, the following formulas are for the following cells:
D8, J8, P8 =IF(OR(W5="Quarterly",W5="Annually"),"Routine","")
H8, L8, ,N8, T8 =IF(OR(W5="Monthly",W5="Quarterly",W5="Annually"),"Routine","")
R8, V8, X8 =IF(OR(W5="Fortnightly",W5="Monthly",W5="Quarterly",W5="Annually"),"Routine","")
D9, J9, P9 =IF(W5="Quarterly","Quarterly",IF(W5="Annually","Quarterly",""))
H9, L9, N9, T9 =IF(W5="Monthly","Monthly",IF(W5="Quarterly","Quarterly",IF(W5="Annually","Quarterly","")))
R9 =IF(W5="Fortnightly","Fortnightly",IF(W5="Monthly","Monthly",IF(W5="Quarterly","Quarterly",IF(W5="Annually","Annually",""))))
V8, X8 =IF(W5="Fortnightly","Fortnightly",IF(W5="Monthly","Monthly",IF(W5="Quarterly","Quarterly",IF(W5="Annually","Quarterly",""))))
D10,H10:X10 =IF((the cell above, eg:D9)="","","YES")

 

[Zot]

I think you are not getting response because most people are just lazy to recreate your spreadsheet especially in picture form. Why not try to install XL2BB and use it to copy and paste your sheet. It will make the capture region to be easily copied and it will also list down cells with formula, thus do not require you to type all those in your question.

Just open new thread I guess. I think someone familiar with this problem will reply.

 

[Peter_SSs]

Just open new thread I guess.

Please don't suggest that in this circumstance, the OP should continue in this thread. Refer to #12 of the Forum Rules and 5 & 6 of the Guidelines

 

[Zot]

Please don't suggest that in this circumstance, the OP should continue in this thread. Refer to #12 of the Forum Rules and 5 & 6 of the Guidelines

I see. My fault. I did not read the rule.

Anyway, can someone ask to previous thread to be deleted because he wanted to rephrase the question and start something new due to no response, perhaps?

 

[Peter_SSs]

can someone ask to previous thread to be deleted because he wanted to rephrase the question and start something new due to no response, perhaps?

An OP could ask that by using the Report button on the post. However, the moderator reviewing the report may or may not grant the request depending on the individual circumstances. In general it is better to simply post again in the same thread with further information/clarification/sample data etc.

@phil_gauge
As well as providing some sample data that we can copy to test with as described by Zot, I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Zot]

An OP could ask that by using the Report button on the post. However, the moderator reviewing the report may or may not grant the request depending on the individual circumstances. In general it is better to simply post again in the same thread with further information/clarification/sample data etc.

Thanks.