HELP! Data Validation & Indirect Function for Subcategory

New Contributor

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 subcategory, the Data Validation Source in cell (B1) is: =INDIRECT($A$1). So each time I change something in list (A1), the list in (B1) changes depending on the selection I made in (A1).


Everything is working fine but the problem is that I have to make this operation 5.000 times and I don't want to manually enter the Data Validation Source each time. When I copy or click-drag the cell (B1) to cell (B2), the cell (B2) acts as a subcategory of cell (A1). In order to make the cell (B2) work as a subcategory of (A2), I have to manually change it in the Data Validation Source.


As I have to make 5.000 cells in column B act as subcategories for their corresponding cells in column A, how am I able to do this without doing it by hand, one by one?


Is there a magic formula which allows me to copy-paste or click-drag the cells and automatically change the source cell with its corresponding one? Like when you click-drag a number or a date and Excel is continuing the dates or numbers automatically. Or when you make a formula like C1=A1-B1 and you copy it to row 2 and the formula automatically becomes C2=A2-B2.


Thank you for your time and understanding.





2 Replies
best response confirmed by Sergei Baklan (MVP)


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.

@Hans Vogelaar 


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!