Create a drop down list in Excel with 2 different work sheets

[Lukas Schenker]

In worksheet A I have defined a drop-down list (source). Now I want to use
this list in around 20 other Excel worksheets. If I want to add the function
with the data validation in worksheet B I always get an error message.
Any idea if there is a solution for this or it is a bug? (Of course if I
copy the data source list into each worksheet it is working).

Thanks for your reply in advance!

 

[Max]

If you use a named/defined range as the DVs' source,
the DVs should work fine in other sheets.

Eg say the defined range is named: MyR
refers to: =Sheet1!$A$1:$A$10
[the source list is assumed in Sheet1's A1:A10]

Then in any other sheet, create the DV with Allow > List, Source: = MyR
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[OssieMac]

Hi Lukas,

When you refer to "20 other Excel worksheets" do you mean worksheets as in
the same workbook like Sheet1, Sheet2 etc or do you mean 20 other workbooks
(separate files)?

If you mean worksheets in the same workbook then enter the source like this:-

=Sheet1!A1:A26

I don't think that you can have the list in other workbooks (files) unless
you create the list somewhere in the same workbook by using links.

 

[OssieMac]

Hi again Lukas,

Max's answer is the correct answer for versions prior to xl2007. I forgot
until I saw his reply.