Copy a cascading dropdown list to other cells.
Solved
Lilibau
Posted messages
32
Status
Member
-
Lilibau Posted messages 32 Status Member -
Lilibau Posted messages 32 Status Member -
Hello everyone,
I have read several articles on cascading drop-down lists but I can't seem to solve the following problem: how to apply my cascading drop-downs (LDC) to other cells using the INDIRECT formula?
I have 3 columns that consist of 3 types of categories:
- Column 1: Level 1 category = backoffice or frontoffice for example
- Column 2: Level 2 category = administration, monitoring, exchange management, document base, ...
- Column 3: Level 3 category = user management, contract management, ...
For col.1, I created a drop-down list named Rub_1 with my 2 choices
For cols. 2 and 3, I created a drop-down list using the INDIRECT formula that refers to column 1 or 2 - example = INDIRECT(L8C1)
I also tried naming the cell = INDIRECT(start)
Everything works fine: I select my choice in col.1, my col.2 only displays the choices related to col.1, and my col.3 only shows the choices related to col.2
The problem is when I want to copy/paste the cell to all other rows (more than 500 rows). The formula stays on the first selected cell and does not apply to the correct line.
I tried putting in the formula “”, or & ... nothing works. The only solution I found is to recreate the formula for each cell ...
Has anyone else encountered this problem? If so, is there a trick I haven't found?
Thank you in advance for your help.
I have read several articles on cascading drop-down lists but I can't seem to solve the following problem: how to apply my cascading drop-downs (LDC) to other cells using the INDIRECT formula?
I have 3 columns that consist of 3 types of categories:
- Column 1: Level 1 category = backoffice or frontoffice for example
- Column 2: Level 2 category = administration, monitoring, exchange management, document base, ...
- Column 3: Level 3 category = user management, contract management, ...
For col.1, I created a drop-down list named Rub_1 with my 2 choices
For cols. 2 and 3, I created a drop-down list using the INDIRECT formula that refers to column 1 or 2 - example = INDIRECT(L8C1)
I also tried naming the cell = INDIRECT(start)
Everything works fine: I select my choice in col.1, my col.2 only displays the choices related to col.1, and my col.3 only shows the choices related to col.2
The problem is when I want to copy/paste the cell to all other rows (more than 500 rows). The formula stays on the first selected cell and does not apply to the correct line.
I tried putting in the formula “”, or & ... nothing works. The only solution I found is to recreate the formula for each cell ...
Has anyone else encountered this problem? If so, is there a trick I haven't found?
Thank you in advance for your help.
1 answer
-
Hello,
it's because you've used an absolute reference.
your L8C1 corresponds to an $A$8.
switch to relative and you won't have any more issues.
best regards-
Hello JvDo,
Thank you for your response and sorry for the delay, I wasn't at my desk.
Could you please clarify how to switch to relative reference? When I click on the cell, the value automatically becomes absolute. I just switched to Excel 2013 and the headers are slightly different: for example, I only have numbers for the columns and no longer letters. I don't know how to remove this absolute value.
Thank you in advance for your help.- Hello,
I don't know the 2013 version, but I assume you can go to your options under "Formulas" and uncheck "R1C1 reference style"
then, regarding absolute references, it's the "$" that makes them so.
remove the "$" and the formula will reference the cells relatively.
you have the F4 keyboard shortcut that allows you to toggle a reference from absolute to relative, passing through relative/absolute and absolute/relative.
best regards
-
-