Forum Discussion

Copper Contributor

Dec 21, 2022

Solved

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 % BAS...

Book1.xlsx10 KB

excel

Formulas and Functions

HansVogelaar
Dec 21, 2022
Brirack77

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.
Book1.xlsx11 KB

HansVogelaar

MVP

Mar 25, 2024

AOSPWB

That is called a dependent data validation drop-down. It requires a different technique.

See Create Dependent Drop-Down Lists

AOSPWB

Brass Contributor

Mar 26, 2024

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.

HansVogelaar
MVP
Mar 26, 2024
AOSPWB

If you include blanks in the list (which I don't recommend), and if the active cell is blank, Excel will select the first blank in the list when you click the drop-down arrow. If the cell is not blank, Excel will select the current value in the list.

Which version of Excel do you use?
- AOSPWB
  Brass Contributor
  Mar 28, 2024
  So, I have taken out the blanks in the tables so that is no longer an issue, but any reason why the "autocomplete" is not working? Be great if they could start to type and it would give suggestions to help speed up the process.
  - HansVogelaar
    MVP
    Mar 28, 2024
    AOSPWB
    
    Select File > Account.
    
    What do you see next to "About Excel"? On my PC, it is
- AOSPWB
  Brass Contributor
  Mar 28, 2024
  apologies for delay - 'life'.... we are using office 365 and I have blanks in the table because I am trying to allow this thing to grow as we need it and I know each site will have areas added and easier for someone to pick a blank, type in a new option than try to redo lists, etc. But if it is going to cause massive issues, then I will eliminate the blanks and my team will just need to monitor.