Forum Discussion
Connecting match to a dropdown list
Hi, I have a question
This is my work sheet.
What I would like is this: when Quads is selected in the dropdownlist (cel B2), i want all the excercise below the name Quads (D3) placed in the red area. So that would be "exercise 1", "exercise 2", "exercise 3" and "exercise 4". When I select Calf instead of Quads I want all the calf excercises in the red area, so that would be "exercise 1", "exercise 2" and "exercise 3".
When I try to do this I won't get the excercise corresponding to the right name or I will get a "0" instead of a blank cell after the last exercise.
I hope u understand the problem and that you will help me.
With kind regards, Luc
It will be better if you indicate on which version of Excel you are - are you with dynamic arrays, LET() function?
Couple of more variants
=INDEX($D$4:$F$36,SEQUENCE(COUNTA(INDEX($D$4:$F$36,0,MATCH($B$1,$D$3:$F$3,0)))),MATCH($B$1,$D$3:$F$3,0)) or =INDEX($D$4:$F$36,ROW(INDIRECT("A1:A"&COUNTA(INDEX($D$4:$F$36,0,MATCH($B$1,$D$3:$F$3,0))))),MATCH($B$1,$D$3:$F$3,0))
9 Replies
SergeiBaklan Riny_van_Eekelen Thank you both for the fast responses and the quick replays. It worked. You both made my day.
Thanks,
Luc
Lucdejong , glad to help
SergeiBaklan Riny_van_Eekelen, Thank you both for your fast response. I really like your ideas, but there is one problem. If I add more exercises the cells in the red collum won't add them automatically. Is there a way I can add excercises in for example quads and that is will show in the red cells?
It will be better if you indicate on which version of Excel you are - are you with dynamic arrays, LET() function?
Couple of more variants
=INDEX($D$4:$F$36,SEQUENCE(COUNTA(INDEX($D$4:$F$36,0,MATCH($B$1,$D$3:$F$3,0)))),MATCH($B$1,$D$3:$F$3,0)) or =INDEX($D$4:$F$36,ROW(INDIRECT("A1:A"&COUNTA(INDEX($D$4:$F$36,0,MATCH($B$1,$D$3:$F$3,0))))),MATCH($B$1,$D$3:$F$3,0))Lucdejong One way would be to expand the named range for each muscle group by one empty cell at the bottom and insert new exercises above that empty cell. Then, the named range will expand with the insertion of a new item and INDIRECT will display the expanded range automatically.
Riny_van_Eekelen when I try to do that i get this
In E7 I added Ex7, but it doesn't show it in B19. What am I doing wrong?
Lucdejong See attached file. It has three possible solutions. It depends on your Excel version and/or your personal preference.
Another DA variant
=LET(group, $B$1, range, $D$4:$F$11, headers, $D$3:$F$3, col, XLOOKUP(group,headers,range), colsize, COUNTA(col), INDEX(col,SEQUENCE(colsize)))
