data validation list from multiple non-contiguous named ranges?

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


0
nospam7515 (2084)
4/15/2004 8:10:15 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
375 Views

Similar Articles

[PageSpeed] 47

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'

Keith R wrote:
> 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?


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
4/15/2004 9:27:50 PM
Works perfectly-
Thanks Debra!!

"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
news:407EFE56.4060303@contexturesXSPAM.com...
> 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'
>


0
nospam7515 (2084)
4/16/2004 1:03:45 PM
You're welcome! Thanks for letting me know.

Keith R wrote:
> Works perfectly-
> Thanks Debra!!
> 
> "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
> news:407EFE56.4060303@contexturesXSPAM.com...
> 
>>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'


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
4/16/2004 6:41:45 PM
Reply:

Similar Artilces: