Automated List using Data Validation

[drake777]

I have problem using list in Data Validation since its not automatically listed.
I want to use automated list, for example (in the attachment) if the country is Trinidad & Tobago, the city list automatically only shown Arnos Vale and Arouca, instead of showing Washington, New York, London, and other cities.

Thanks before.

[FDibbins]

Attached is a sample file I put together that shows how to do what you want. It uses names ranges for each level and sub-level, and also uses INDIRECT() to pull in the results

[drake777]

Attached is a sample file I put together that shows how to do what you want. It uses names ranges for each level and sub-level, and also uses INDIRECT() to pull in the results

If i want using "&" ??

[FDibbins]

What do you mean?

[drake777]

Like Trinidad & Tobago.
How to use substitute on them??

[FDibbins]

You mean, if they swap them around?

[drake777]

I mean how to use both " " (space) and "&".
The substitute only replace " " into "" right?
What if in some category i want to have "&" as well, because i can't use "&" in define name

[FDibbins]

The substitute only replace " " into "" right?

No, range names dont allow the use of a space in the name, so I am replacing (just) the space with underscore _

[drake777]

Yes, i get how to get it done with your example.
the next question is if my category is "Mammals &" instead of "Mammals yes".
Can i change the "yes" into "&"?

[drake777]

Repost, deleted

[FDibbins]

No, range names wont accept special characters. Seeing as this will be a DD and wont need typing, can you use AND instead of &? This will simplify it a lot

[drake777]

Yes, i can change it into "AND", i'm just curious.
Maybe it can using INDIRECT(VLOOKUP(.....)) , but i figure it will a lot complicated.

THANK YOU SO MUCH FDibbins.

[FDibbins]

Im happy to help

I always try and keep things to their simplest level. yes, there are probably a few work-around so you could use ? but really, using AND would be teh exact same thing, and it is not being typed anyway