Forum Discussion
Data validation range based on another cell contents
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.
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.
- Thank you! Works rather well. Only one item I am trying to understand... I needed to make the list include a few 'blanks' so that we can add on the second drop down and now, after selecting the "site", when going to the next one, sometime when I click on the pull down, it will start by showing the first in the list and sometimes it starts with the blanks which requires me to scroll to the top. The "Ignore" blanks is selected. also - any reason the auto complete is not working? I.E. on the Using Ohio instead of OH as the actual name.
- works a treat thank you! (I did a lot of named ranges to not get this to work in another way 😕 )
