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

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!

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

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
---
"Lukas Schenker" wrote:
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!

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

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.

--
Regards,

OssieMac


"Lukas Schenker" wrote:

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!

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

Hi again Lukas,

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

--
Regards,

OssieMac


"OssieMac" wrote:

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.

--
Regards,

OssieMac


"Lukas Schenker" wrote:

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!