Dependent Data Validation Lists and the rule of no spaces in named ranges

Assumption: I assume you already know how to setup a data validation list that is dependent on the text of another cell. If not searching "dependent data validation lists" will bring you up to speed within a few minutes.

Since named ranges can't have spaces, using =INDIRECT(A1) in data validation requires the text in A1 has no spaces. This is fine for single word text, but for something like "Northern States" we have to go with something like "NorthernStates" or "Northern_States" or something to remove the space between the two words. I wanted a workaround for this to maintain a more neater layout. I came up with this formula for my data validation formula,

Formula:

Please Login or Register  to view this content.

In my vlookupup_table I have the text with the space in the first column, and the text with the space removed in the second column, which corresponds with my named range titles.

I realize I am discovering something that many others have already discovered, but I find it useful and hope someone else does too.

Hello,
I have an issue with creating a dependent drop down lists, because one of the lists contains numbers.
I can not use indirect formula, because I can not use numbers in the name box field.

You can find as attached the example file.
Please help me.

Regards,
Aneliya