SOLVED

Dropdown list depend on another dropdown list and the range would auto add in name

%3CLINGO-SUB%20id%3D%22lingo-sub-2491999%22%20slang%3D%22en-US%22%3EDropdown%20list%20depend%20on%20another%20dropdown%20list%20and%20the%20range%20would%20auto%20add%20in%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2491999%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20find%20a%20way%20to%20auto%20add%20the%20new%20value%20into%20the%20Name%2C%20I%20find%20the%20below%20fomula%20would%20let%20me%20do%20this.%20So%20I%20set%20up%20all%20the%20Name%20with%20this%20kind%20of%20fomula%3C%2FP%3E%3CP%3Eeg%3C%2FP%3E%3CP%3E%3DOFFSET(Selection_Data!%24B%242%2C0%2C0%2CCOUNTA(Selection_Data!%24B%3A%24B)-1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20I%20would%20like%20to%20set%20up%20one%20dropdown%20list%20depend%20on%20another%20dropdown%20list%2C%20I%20use%20the%20indirect()%20in%20the%20second%20dropdown%20list%2C%20but%20it%20see%20can%20not%20run.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMay%20I%20know%20how%20would%20I%20do%20the%20about%20two%20target%20together%20%3F%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2491999%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2492116%22%20slang%3D%22en-US%22%3ERe%3A%20Dropdown%20list%20depend%20on%20another%20dropdown%20list%20and%20the%20range%20would%20auto%20add%20in%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2492116%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1089783%22%20target%3D%22_blank%22%3E%40TigerEmperor%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20workbook%20for%20an%20example.%20You%20can%20view%20the%20definition%20of%20the%20defined%20names%20in%20Formulas%20%26gt%3B%20Name%20Manager.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2492117%22%20slang%3D%22de-DE%22%3ESubject%3A%20Dropdown%20list%20depend%20on%20another%20dropdown%20list%20and%20the%20range%20would%20auto%20add%20in%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2492117%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1089783%22%20target%3D%22_blank%22%3E%40TigerEmperor%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.herber.de%2Fxlfaq%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EDropDown%203%20contiguous%3C%2FA%3E%3C%2FP%3E%3CP%3EHere%20is%20an%20example%20with%203%20dependent%20dropdown%20lists.%20Has%20been%20picked%20out%20of%20my%20little%20archives.%20Is%20from%20an%20Internet%20site.%3CBR%20%2F%3ESent%20as%20downloaded%20(everything%20is%20in%20German)%2C%20should%20work.%3CBR%20%2F%3EIt%20worked%20in%20a%20short%20test%20with%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I would like to find a way to auto add the new value into the Name, I find the below fomula would let me do this. So I set up all the Name with this kind of fomula

eg

=OFFSET(Selection_Data!$B$2,0,0,COUNTA(Selection_Data!$B:$B)-1)

 

Then I would like to set up one dropdown list depend on another dropdown list, I use the indirect() in the second dropdown list, but it see can not run. 

 

May I know how would I do the about two target together ? Thanks.

5 Replies
best response confirmed by TigerEmperor (New Contributor)
Solution

@TigerEmperor 

See the attached workbook for an example. You can view the definition of the defined names in Formulas > Name Manager.

@TigerEmperor 

 

DropDown 3 contiguous

Here is an example with 3 dependent dropdown lists. Has been picked out of my little archives. Is from an Internet site.
Sent as downloaded (everything is in German), should work.
It worked in a short test with me.

 

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

Hi, Vogelaar, Good Afternoon. It is wonderful, it seem work, may I know if there is method to make the list in vertical form, so I would add a data under the last one in list simply. Thanks.

@TigerEmperor 

The advantage of the method in the sample workbook that I posted is that you need just three defined names: States, Counties, and Towns.

With the other method, you need an ever increasing set of defined names: one for each state, and one for each county.

Dear Vogelaar
Good Morning.
Many thanks for your advise,
Yes, you are right, it make the defined names less, I would use the sample method you give me,
Once more thanks for you!!!