Forum Discussion

DreCpt's avatar
DreCpt
Copper Contributor
Aug 30, 2022
Solved

HELP! Data Validation & Indirect Function for Subcategory

Dear members ... I need your help! 🙂   I have created a dropdown list on cell (A1) and another dropdown list on cell (B1). (B1) list acts as a subcategory of (A1) list. In order to act as a subcat...
excel
  • HansVogelaar's avatar
    HansVogelaar
    Aug 30, 2022

    DreCpt 

    Select B1:B5000.

    Remove the existing data validation rules, if any.

    Set up data validation of type List with formula =INDIRECT($A1)

    Please note that there is no $ sign before the row number 1. This makes the row number relative: Excel will automatically use =INDIRECT($A2) in B2, =INDIRECT($A3) in B3, etc.

HansVogelaar's avatar
HansVogelaar
MVP
Aug 30, 2022

DreCpt 

Select B1:B5000.

Remove the existing data validation rules, if any.

Set up data validation of type List with formula =INDIRECT($A1)

Please note that there is no $ sign before the row number 1. This makes the row number relative: Excel will automatically use =INDIRECT($A2) in B2, =INDIRECT($A3) in B3, etc.

  • DreCpt's avatar
    DreCpt
    Copper Contributor
    Aug 31, 2022

    HansVogelaar 

     

    Hans, you`re a lifesaver! 🙂

     

    Thank you very much, it works perfectly!

     

    I knew that $ blocks the column/row but somehow I haven't thought about it.

     

    Thank you and have a lovely day!

Resources