data validation list from multiple non-contiguous named ranges?

[Keith R]

I have three non-contiguous ranges, named: Locations, AltLocations,
AddLocations

I have a data validation cell (list) that I need to include all three of
these ranges as options, but XL XP gives an error that source has to be all
one row, all one column, or a comma delimited list.

I added all three named locations into a new range: AllLocations =
Locations,AltLocations,AddLocations
when I click on the name, it highlights all three ranges, but when I try to
add that into my new column it gives me [#value], which is strange because
if I use one of the original ranges in the column (C1=Locations, then drag
down) it gives me the contents of that named range, in those cells.

What is the best workaround? If the workaround is to add another (hidden)
column and put all these items in one column, how can I add all three
dynamically to a hidden column without blank spaces, since the source ranges
will be updated by users and may vary in size?

Not sure where I'm going wrong... help?

Thanks in advance,
Keith R

 

[Debra Dalgleish]

I've added a sample workbook here, that combines three dynamic lists
into a master list, and uses the master for the data validation list source:

http://www.contextures.com/excelfiles.html

Under 'Data Validation', look for 'Combine Multiple Lists into One'

I have three non-contiguous ranges, named: Locations, AltLocations,
AddLocations

I have a data validation cell (list) that I need to include all three of
these ranges as options, but XL XP gives an error that source has to be all
one row, all one column, or a comma delimited list.

I added all three named locations into a new range: AllLocations =
Locations,AltLocations,AddLocations
when I click on the name, it highlights all three ranges, but when I try to
add that into my new column it gives me [#value], which is strange because
if I use one of the original ranges in the column (C1=Locations, then drag
down) it gives me the contents of that named range, in those cells.

What is the best workaround? If the workaround is to add another (hidden)
column and put all these items in one column, how can I add all three
dynamically to a hidden column without blank spaces, since the source ranges
will be updated by users and may vary in size?

 

[Keith R]

Works perfectly-
Thanks Debra!!

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know.

Works perfectly-
Thanks Debra!!