Troubleshooting =Indirect(Substitute(A4:A:8," ","_"))

[cocottelabroue]

Hello,

*SOLVED* 2 solutions provided

I would like to have dependent drop-down menus.
I have one column for THEMES and another for TASKS. The tasks are associated to themes. I should get a different list of tasks for each theme I select.

So far, I would say that I have 90% of the solution.
I created my THEMES column, cell A4:A8, using the classic drop-menu. There are 13 themes.
I have created my TASKS column, cell B4:B8, using Formulas-Create from Selection and Data Validation List.

It works for the most part. Here is the problem: only 11 of 13 themes work. I mean that only 11 of the 13 drop-down menus of tasks associated to themes appear. I cannot see the first theme, which is SELECT, nor can I see the last, which is RESEARCH. (See attached)

[Bo_Ry]

Please use Data validation list at B4

=OFFSET($E$3,,MATCH(A4,$E$2:$Q$2,)-1,COUNTIFS(INDEX($E$3:$Q$13,,MATCH(A4,$E$2:$Q$2,)),"*??"))

[cocottelabroue]

Thank you for your attempt, Bo_Ry

1) I do not understand the =Offset function you are recommending
2) Also, I'm not sure if I missed something, but I looked at your solution and I can no longer choose a theme in cell A4:A8 or the associated tasks, all that is available to me is a drop-down menu with the word select as a choice.

[Bo_Ry]

I have no idea why you can't choose a theme
Image below is from file in post #2

DV.jpg

[BMV]

cocottelabroue
you use option with named range but range was named "Research" and "Research " in the a4. After substitute it will be "Research_" so the method is ok but data for first list is wrong. you can correct data or use TRIM function
=Indirect(Substitute(trim(A4:A8)," ","_")) also a4:a8 is working but =Indirect(Substitute(trim(A4)," ","_")) better.

Offset is recommended only because the options use simple ranges without any name and it easy manage and create dynamic lists.

[cocottelabroue]

Bo_Ry!!! I looked it over again, and it worked! I guess the problem was between the screen and the chair.
Thank you.

[cocottelabroue]

BMV! Thank you. Your formula was a little easier for a newbie like me to understand. Although Bo_Ry's formula worked, I was unable to reproduce it in my original document for my lack of knowledge and skill. Too complicated.