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

[TigerEmperor]

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.

[#2 · Accepted solution]

@TigerEmperor 

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

[#3]

@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.

[#4]

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.

[#5]

@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.

[#6]

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!!!