SOLVED

Can we make a dependent dropdown based on the value of other cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2316052%22%20slang%3D%22en-US%22%3ECan%20we%20make%20a%20dependent%20dropdown%20based%20on%20the%20value%20of%20other%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2316052%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20get%20a%20dropdown%20list%20based%20on%20the%20value%20of%20another%20cell.%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20already%20got%20a%20formula.%20However%20I%20can't%20manually%20enter%20the%20value%2C%20I've%20to%20select%20it%20via%20list%20only.%20Can%20it%20be%20sorted%3F%26nbsp%3B%3CBR%20%2F%3EI'm%20attaching%20the%20file%3CBR%20%2F%3E%3CBR%20%2F%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%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2316052%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2316178%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20we%20make%20a%20dependent%20dropdown%20based%20on%20the%20value%20of%20other%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2316178%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F523656%22%20target%3D%22_blank%22%3E%40Nishkarsh31%3C%2FA%3E%26nbsp%3BThe%20solution%20would%20be%20not%20to%20use%20LET.%20Don't%20really%20understand%20why%2C%20but%20it%20seems%20overly%20complicated%20to%20begin%20with.%20Just%20repeat%20the%20code%20for%20the%20%22yo%22%20variable.%20Then%2C%20the%20formula%20for%20the%20data%20validation%20will%20look%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DOFFSET('Exp%20List'!%24A%242%2C0%2CXMATCH(D6%2C'Exp%20List'!%24A%241%3A%24H%241)-1%2CCOUNTA(OFFSET('Exp%20List'!%24A%242%2C0%2CXMATCH(D6%2C'Exp%20List'!%24A%241%3A%24H%241)-1%2C100)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eor%2C%20if%20you%20would%20use%20some%20named%20ranges%20for%20the%20categories%20and%20starting%20point%20it%20could%20become%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DOFFSET(start%2C0%2CXMATCH(D6%2Ccategories)-1%2CCOUNTA(OFFSET(start%2C0%2CXMATCH(D6%2Ccategories)-1%2C100)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BNot%20very%20complex%20and%20not%20really%20necessary%20to%20use%20LET.%20and%20it%20will%20allow%20you%20to%20enter%20the%20sub-category%20by%20typing.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2317022%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20we%20make%20a%20dependent%20dropdown%20based%20on%20the%20value%20of%20other%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2317022%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20sir.%3CBR%20%2F%3EI%20would%20never%20have%20been%20able%20to%20guess%20why%20LET%20is%20the%20one%20that's%20not%20working%20in%20his%20situation%3C%2FLINGO-BODY%3E
Contributor

I want to get a dropdown list based on the value of another cell.

I've already got a formula. However I can't manually enter the value, I've to select it via list only. Can it be sorted? 
I'm attaching the file

@Sergei Baklan @Peter Bartholomew @Detlef Lewin @Riny_van_Eekelen 

2 Replies
best response confirmed by Nishkarsh31 (Contributor)
Solution

@Nishkarsh31 The solution would be not to use LET. Don't really understand why, but it seems overly complicated to begin with. Just repeat the code for the "yo" variable. Then, the formula for the data validation will look like this:

=OFFSET('Exp List'!$A$2,0,XMATCH(D6,'Exp List'!$A$1:$H$1)-1,COUNTA(OFFSET('Exp List'!$A$2,0,XMATCH(D6,'Exp List'!$A$1:$H$1)-1,100)))

or, if you would use some named ranges for the categories and starting point it could become like this:

=OFFSET(start,0,XMATCH(D6,categories)-1,COUNTA(OFFSET(start,0,XMATCH(D6,categories)-1,100)))

 Not very complex and not really necessary to use LET. and it will allow you to enter the sub-category by typing.

Thank you so much sir.
I would never have been able to guess why LET is the one that's not working in his situation