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 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
  Version 2302 (Build 16130.20846 Click-to-Run)
  Semi-Annual Enterprise Channel
  - HansVogelaar
    MVP
    Mar 28, 2024
    AOSPWB
    
    That explains it. AutoComplete was introduced in version 2308.
    
    Your version is older. You'll have to wait until you are updated to 2308.