Named Range Cannot Be found (even though it exists)

Indirect has to have quotes around the desired range to be referenced, so that's probably your main issue.

Additionally, I've had some issues with conditional validation where i think you can only reference a range, not a calculated range -- but spill ranges are okay. Again, I am not 100% I've understood the limitation, but since spill range has always worked for me, that's been my route for such things. I've attached a sample file (no vba) where cell G4 has a country selection (Germany/USA) and the cell below G5 has data validation that's driven off of the above cell showing only cities within the country of choice.

Steps to create

• Create list of US cities from B1:B3 of Boston, Atlanta, Dallas
• Create German cities in C1:C2 of Munich, Berlin
• In cell A1 enter this formula: =if(G4="USA",B1:B3,C1:C2)
• Create a named range of listValidation that is =Sheet1!$A$1#  (important to note the hashtag)
• For you data validation rule in G5 set the validation rule for List =indirect("listValidation")

Further ideas are that you could have cell A1 be more dynamic, filtering all of columns b and c based on values not being blank. Best choice would probably be a single table column.