Forum Discussion
Data validation range based on another cell contents
Hi All,
I am having trouble with this one and feel like it should be easier than it is (for me)
I want to have two data validation columns, Column C is whole no. 1 thru 6, Column D is a range % BASED on the result in Column C. So if C2 = 1, D2 only offers an input of 0%. However if C3 = 4, D3 offers an input of between 100% & 130%. This could be as a dropdown on just valid range between min & max.
Example attached and thank you.
Split the min and max values into separate columns:
Next, select D5:D10.
On the Data tab of the ribbon, click Data Validation.
Select Decimal from the Allow drop down, and between from the Data drop down.
In the Minimum box, enter the formula =VLOOKUP(C5,$C$15:$F$20,3,FALSE)
In the Maximum box, enter the formula =VLOOKUP(C5,$C$15:$F$20,3,FALSE)
If desired, activate the Input Message and Error Alert tabs and enter appropriate messages.
Finally, click OK.
See the attached workbook.
12 Replies
Split the min and max values into separate columns:
Next, select D5:D10.
On the Data tab of the ribbon, click Data Validation.
Select Decimal from the Allow drop down, and between from the Data drop down.
In the Minimum box, enter the formula =VLOOKUP(C5,$C$15:$F$20,3,FALSE)
In the Maximum box, enter the formula =VLOOKUP(C5,$C$15:$F$20,3,FALSE)
If desired, activate the Input Message and Error Alert tabs and enter appropriate messages.
Finally, click OK.
See the attached workbook.
Question.. I have a similar issue. in one cell the user is going to select a "location" and based on that location, I need the validation to move from one set of options to the next... I.E. if C2 = "OH", then use the list between D1:D20, if C2="TN" then use the list between E1:E50, if C2="NC", then use the list between F1:F15, if C2="KY" then use the list between G1:G6. Each location has a specific areas that can be chosen from and based on what area they are in, want to limit the validation to their specific choices. Is this possible?
That is called a dependent data validation drop-down. It requires a different technique.
- works a treat thank you! (I did a lot of named ranges to not get this to work in another way 😕 )
