Aug 30 2022 02:21 PM
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.
Aug 30 2022 02:47 PM
SolutionSelect 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.
Aug 31 2022 01:07 AM
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!
Aug 30 2022 02:47 PM
SolutionSelect 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.