A question about dropdown lists

%3CLINGO-SUB%20id%3D%22lingo-sub-1416626%22%20slang%3D%22en-US%22%3EA%20question%20about%20dropdown%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416626%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image001.png%22%20style%3D%22width%3A%20645px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194319i5AB3283405D83CE6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image001.png%22%20alt%3D%22image001.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20have%20a%20question%20about%20dropdown%20lists.%20I%20want%20to%20make%203%20dropdown%20lists%20(the%20last%20one%20dependent).%20Cell%20A12%20and%20B12%20are%20both%20drop%20down%20lists%20(not%20dependent).%20In%20the%20drop%20down%20list%20I%20have%20the%20options%20from%20A3%3AA5%20and%20in%20B12%20I%20have%20the%20options%20of%20B3%3AB5.%20If%20I%20choose%20Quadriceps%20in%20A12%20and%20Balance%20in%20B12%20I%20want%20all%20the%20exercises%20of%20the%20collum%20QuadricepsBalance.%20So%20that%20would%20be%205%20exercises.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20there%20is%20a%20way%20tot%20do%20this.%20I'm%20not%20bound%20to%20the%20lay-out%20so%20I%20can%20change%20whatever%20I%20want%20to%20make%20it%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1416626%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416660%22%20slang%3D%22en-US%22%3ERe%3A%20A%20question%20about%20dropdown%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416660%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F658432%22%20target%3D%22_blank%22%3E%40Luc_de_Jong%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20your%20version%20of%20Excel%20supports%20dynamic%20arrays%2C%20for%20such%20model%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20719px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194321i7E7E276A5B92A72C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ethe%20function%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFNA(FILTER(%0A%20%20%20%20INDEX(%24C%243%3A%24K%249%2C0%2C(MATCH(%24A%2412%2C%24A%242%3A%24A%244%2C0)-1)*3%2BMATCH(%24B%2412%2C%24B%242%3A%24B%244%2C0))%2C%0A%20%20%20%20INDEX(%24C%243%3A%24K%249%2C0%2C(MATCH(%24A%2412%2C%24A%242%3A%24A%244%2C0)-1)*3%2BMATCH(%24B%2412%2C%24B%242%3A%24B%244%2C0))%26lt%3B%26gt%3B0%0A)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416671%22%20slang%3D%22en-US%22%3ERe%3A%20A%20question%20about%20dropdown%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416671%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI%20already%20tried%20that.%20The%20problem%20is%20I%20can't%20use%20the%20filter%20command%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416688%22%20slang%3D%22en-US%22%3ERe%3A%20A%20question%20about%20dropdown%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416688%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F658432%22%20target%3D%22_blank%22%3E%40Luc_de_Jong%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20convert%20above%20formula%20to%20not-DA%20one%2C%20for%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20532px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194322iC2627EE300714514%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ein%20C12%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(%0A%20%20%20%20INDEX(%0A%20%20%20%20%20%20%20%20INDEX(%24C%243%3A%24K%249%2C0%2C(MATCH(%24A%2412%2C%24A%242%3A%24A%244%2C0)-1)*3%2BMATCH(%24B%2412%2C%24B%242%3A%24B%244%2C0))%2C%0A%20%20%20%20%20%20%20%20AGGREGATE(15%2C6%2C1%2F(INDEX(INDEX(%24C%243%3A%24K%249%2C0%2C(MATCH(%24A%2412%2C%24A%242%3A%24A%244%2C0)-1)*3%2BMATCH(%24B%2412%2C%24B%242%3A%24B%244%2C0))%2CROW()-ROW(%24C%2411))%26lt%3B%26gt%3B%22%22)*ROW()-ROW(%24C%2411)%2C1)%0A)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down.%20See%20in%20Sheet2%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416690%22%20slang%3D%22en-US%22%3ERe%3A%20A%20question%20about%20dropdown%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416690%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F658432%22%20target%3D%22_blank%22%3E%40Luc_de_Jong%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20C12%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000080%22%3E%3CSTRONG%3E%3DT(HLOOKUP(B%2412%2CINDEX(%243%3A%243%2CMATCH(A%2412%2C%242%3A%242%2C0))%3AK%24100%2CROWS(%241%3A2)%2C0))%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20copied%20down%20until%20you%20start%20to%20get%20blanks%20for%20the%20results.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416730%22%20slang%3D%22en-US%22%3ERe%3A%20A%20question%20about%20dropdown%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416730%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20respons.%20It%20works.%20I%20wonder%20if%20it%20is%20possible%20to%20make%20C12%20a%20drop%20down%20list%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1417224%22%20slang%3D%22en-US%22%3ERe%3A%20A%20question%20about%20dropdown%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1417224%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F658432%22%20target%3D%22_blank%22%3E%40Luc_de_Jong%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20added%20maxExercises%20parameter%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20628px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194366iD2AEE91FA8118470%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20for%20the%20list%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20348px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194367iD373A73FA9DF9626%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eis%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DOFFSET(%24C%243%2C0%2C(MATCH(%24A%2412%2C%24A%242%3A%24A%244%2C0)-1)*3%2BMATCH(%24B%2412%2C%24B%242%3A%24B%244%2C0)-1%2CCOUNTA(OFFSET(%24C%243%2C0%2C(MATCH(%24A%2412%2C%24A%242%3A%24A%244%2C0)-1)*3%2BMATCH(%24B%2412%2C%24B%242%3A%24B%244%2C0)-1%2CmaxExercises)))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ein%20third%20sheet%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1417391%22%20slang%3D%22en-US%22%3ERe%3A%20A%20question%20about%20dropdown%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1417391%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BDear%20Sergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20so%20so%20so%20greatfull%20for%20your%20help.%20Thank%20you%20for%20putting%20the%20time%20into%20this%20and%20not%20only%20explain%20it%2C%20but%20also%20creating%20the%20formules%20and%20the%20table.%20I%20was%20struggling%20with%20it%20for%20quite%20some%20time%20now.%20Once%20again%20thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELuc%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1417414%22%20slang%3D%22en-US%22%3ERe%3A%20A%20question%20about%20dropdown%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1417414%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F658432%22%20target%3D%22_blank%22%3E%40Luc_de_Jong%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Luc%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20an%20alternative%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20relies%20on%20setting%20up%20NAMES%20(see%20Formulas-%26gt%3BName%20Manager)%20for%20each%20group%20e.g.%20%22HamstringBalance%22%26nbsp%3B%20to%20cover%20those%20exercises%3B%20%22HamstringStrength%22%20for%20those%20exercises%20and%20so%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20dependent%20drop%20down%20then%20using%20the%20Indirect%20function%20and%20the%20options%20you%20select%20in%20the%20first%20two%20column%20to%20point%20to%20the%20correct%20list%20of%20exercises.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

image001.png

I have a question about dropdown lists. I want to make 3 dropdown lists (the last one dependent). Cell A12 and B12 are both drop down lists (not dependent). In the drop down list I have the options from A3:A5 and in B12 I have the options of B3:B5. If I choose Quadriceps in A12 and Balance in B12 I want all the exercises of the collum QuadricepsBalance. So that would be 5 exercises.

 

I hope there is a way tot do this. I'm not bound to the lay-out so I can change whatever I want to make it work.

 

8 Replies
Highlighted

@Luc_de_Jong 

If your version of Excel supports dynamic arrays, for such model

image.png

the function could be

=IFNA(FILTER(
    INDEX($C$3:$K$9,0,(MATCH($A$12,$A$2:$A$4,0)-1)*3+MATCH($B$12,$B$2:$B$4,0)),
    INDEX($C$3:$K$9,0,(MATCH($A$12,$A$2:$A$4,0)-1)*3+MATCH($B$12,$B$2:$B$4,0))<>0
),"")
Highlighted

@Sergei Baklan I already tried that. The problem is I can't use the filter command

Highlighted

@Luc_de_Jong 

If convert above formula to not-DA one, for this

image.png

in C12

=IFERROR(
    INDEX(
        INDEX($C$3:$K$9,0,(MATCH($A$12,$A$2:$A$4,0)-1)*3+MATCH($B$12,$B$2:$B$4,0)),
        AGGREGATE(15,6,1/(INDEX(INDEX($C$3:$K$9,0,(MATCH($A$12,$A$2:$A$4,0)-1)*3+MATCH($B$12,$B$2:$B$4,0)),ROW()-ROW($C$11))<>"")*ROW()-ROW($C$11),1)
),"")

and drag it down. See in Sheet2 attached.

Highlighted

@Luc_de_Jong 

 

Hi,

 

In C12:

 

=T(HLOOKUP(B$12,INDEX($3:$3,MATCH(A$12,$2:$2,0)):K$100,ROWS($1:2),0))

 

and copied down until you start to get blanks for the results.

 

Regards

Highlighted

@Sergei Baklan Thank you for your respons. It works. I wonder if it is possible to make C12 a drop down list

Highlighted

@Luc_de_Jong 

I added maxExercises parameter

image.png

formula for the list

image.png

is

=OFFSET($C$3,0,(MATCH($A$12,$A$2:$A$4,0)-1)*3+MATCH($B$12,$B$2:$B$4,0)-1,COUNTA(OFFSET($C$3,0,(MATCH($A$12,$A$2:$A$4,0)-1)*3+MATCH($B$12,$B$2:$B$4,0)-1,maxExercises)))

in third sheet attached

Highlighted

@Sergei Baklan Dear Sergei,

 

I'm so so so greatfull for your help. Thank you for putting the time into this and not only explain it, but also creating the formules and the table. I was struggling with it for quite some time now. Once again thanks.

 

Luc

Highlighted

@Luc_de_Jong 

 

Hi Luc

 

I attached an alternative solution.

 

This relies on setting up NAMES (see Formulas->Name Manager) for each group e.g. "HamstringBalance"  to cover those exercises; "HamstringStrength" for those exercises and so on.

 

The dependent drop down then using the Indirect function and the options you select in the first two column to point to the correct list of exercises.

 

Hope this helps.

 

Peter