Automated data validation

phil_gauge

New Member
Joined
Jan 4, 2017
Messages
19
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")
1613446691494.png
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
972
Office Version
  1. 2016
Platform
  1. Windows
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. :)
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
972
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,391
Office Version
  1. 365
Platform
  1. Windows
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’)
 
  • Like
Reactions: Zot

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
972
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,222
Messages
5,623,472
Members
415,973
Latest member
johnemaile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top