Excel Lists

  • Thread starter Thread starter Roy Sites
  • Start date Start date
R

Roy Sites

Want to create a drop-down list from a data range in
another Excel workbook. When I try this the Validation
menu gives me an error that says it cannot use data from
another workbook or even another worksheet. How can I
get around this?
 
Thanks, but this is the same information as supplied with
Micorsoft help and does not work. Any other suggestions?
 
Hi Roy!

You can use data from another worksheet in a drop down list as long as
you name that list and refer to it by name.

You could use standard referencing to bring the other workbook's list
to the subject workbook, name it and use that name.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thanks, but this is the same information as supplied with
Micorsoft help and does not work. Any other suggestions?
Click to expand...
...

Did you actually perform all the steps as listed?

FWIW, under Excel 97, if I create the defined name LST in workbook foo.xls
referring to =[bar.xls]SheetWhatever!A1:A10 in workbook bar.xls, then back in
foo.xls I select a cell and run Data > Validation, switch to the Settings tab in
the Data Validation dialog, and select List in the Allow field and enter =LST in
the Source field, then click OK, the active cell had a drop-down control on the
right edge, and when I click on it I see all the entries from the range in
bar.xls. This doesn't work on your system?
 
Hi Roy!

Debra's instructions work OK for me.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Addendum: using defined names to refer to ranges in other workbooks only works
when those other workbooks are open. If they're not open, then the only way to
use their contents in validation lists in other workbooks is to enter external
reference formulas in some range in the current workbook, then use that range as
the source for the validation list.

Note that Debra's site is quite clear about the requirement that the other
workbook be open.
 
Roy

What is "does not work"? Nothing happens? Wrong data? Error message?

Go back to Debra's instructions, read again then re-try.

Referring to a Named Range in another workbook DOES work if the workbook is
open.

Gord Dibben Excel MVP
 
I have the Excel 2003 file with the data open, I create a
name in the new Excel workbook that references the data
in the other workbook. I then use the Data Validation
with the Allow box set to List. I then set the source to
the name defined preceded with an equal sign. When I
click "Ok" I get the following error message:
"You may not use references to other worksheets or
workbooks for Data Validation criteria."

This does work when I use a different worksheet, but I
get the above error message when I try to use a different
workbook.
 
Roy
I have the Excel 2003 file with the data open, I create a
name in the new Excel workbook that references the data
in the other workbook.
Click to expand...

Do not create the name in the new workbook. Create it in the workbook that
contains the list.

With data file open, select your list and Insert>Name>Define

In the new workbook, your DV list will refer to that name.
I then use the Data Validation with the Allow box set to List.
I then set the source to the name defined preceded with an equal sign.
Click to expand...

Both workbooks must be open.

Gord
 
Back
Top