Creating a List
 

Excel 2003

Hello,

I am trying to create a drop-down list in a workbook based on a range of
cells in another workbook via the Data, Validation menu commands. I have
followed the steps as specified in the Help system which state that I can
reference a "different worksheet in a different workbook." However when I
try to create the list I receive the message that you cannot use external
references (I am using a named range created in the workbook that references
the external cells).

Thanks for any help you can provide.

Creating a List
 

Not sure about using data validation for this, but you could use a
combo box to select from a list that lives in a different workbook.



Jim




Jeff Bendert wrote:
Excel 2003

Hello,

I am trying to create a drop-down list in a workbook based on a range of
cells in another workbook via the Data, Validation menu commands. I have
followed the steps as specified in the Help system which state that I can
reference a "different worksheet in a different workbook." However when I
try to create the list I receive the message that you cannot use external
references (I am using a named range created in the workbook that references
the external cells).

Thanks for any help you can provide.

Creating a List
 

How would I do that?

"jim" wrote:

Not sure about using data validation for this, but you could use a
combo box to select from a list that lives in a different workbook.



Jim




Jeff Bendert wrote:
Excel 2003

Hello,

I am trying to create a drop-down list in a workbook based on a range of
cells in another workbook via the Data, Validation menu commands. I have
followed the steps as specified in the Help system which state that I can
reference a "different worksheet in a different workbook." However when I
try to create the list I receive the message that you cannot use external
references (I am using a named range created in the workbook that references
the external cells).

Thanks for any help you can provide.

Creating a List
 

You can get more information by reading up on Excel Forms, but this
should get you started. Also, this will only work if you want a single
menu (vs. the same menu appearing in multiple cells*).

First, show the forms toolbar: View Toolbars Forms

Then get your menu started:

-Click on the "Combo Box" icon (mouse over the new toolbar until this
comes up)
-Click and drag (just like a text box) into your workbook
-Right click the menu and choose "Format Control"
-For the "input range" select the data in the workbook you want
to appear in the list
-For the output range, choose a cell in the existing workbook you
can reference
-Specify the number of rows you want to appear in the list
-Then Okay out of it

When you select from the menu, the output range will change to a
numeric (that can be used in conjunction with a vlookup against the
list if you need to translate to some other value).

*If you want several cells to contain the list, you might want to find
a copy to copy the list from your other workbook into the active one
(into hidden cells?) and then stick with the validation option you
originally tried, but within the same book now.

At least that's a couple of ideas anyway.



Jim


Jeff Bendert wrote:
How would I do that?

"jim" wrote:

Not sure about using data validation for this, but you could use a
combo box to select from a list that lives in a different workbook.



Jim




Jeff Bendert wrote:
Excel 2003

Hello,

I am trying to create a drop-down list in a workbook based on a range of
cells in another workbook via the Data, Validation menu commands. I have
followed the steps as specified in the Help system which state that I can
reference a "different worksheet in a different workbook." However when I
try to create the list I receive the message that you cannot use external
references (I am using a named range created in the workbook that references
the external cells).

Thanks for any help you can provide.

Creating a List
 

Good day Jim.

First this link will provide you a visual plus a good starting point.

http://www.contextures.on.ca/xlDataVal05.html


Second here is a wriiten procedure if you are running Excel 2003:


Ensure that you have "BOTH" Workbooks open in order for the
Data|Validation|Allow|List source to function properly.

Assign a 'Name' to the 'List' that was created in the source Workbook that
the target Workbook can read.

To accomplish this, open 'target' Workbook, Use the following menu items:
Insert|Name|Define

In the "Names In Workbook" box, enter a name of your chosen.
Ex: ValidDepts

Select the *entire* contents of the "Refers To" box (turns black),
And switch to the source Workbook, and select the range of cells that
contains
your data list.

Very important item he Backspace until the explanation point. Now enter
the 'Name' range of the 'List'

Click <OK


NOW... set-up your drop-down list.

Select the cell to which you desire your drop-down list and use the
following menu items:
Data|Validation|Allow click on 'List'.

In the "Source" box, enter your chosen name:
Ex: =ValidDepts (Ensure that you use the equal sign)

Click <OK

And you're done!

Tony D. Abel

"Excellence is an art won by training and habitation. We do not act rightly
because we have virtue or excellence, but rather we have those because we
have acted rightly. We are what we repeatedly do. Excellence, then is not an
act but a habit."




"jim" wrote:

You can get more information by reading up on Excel Forms, but this
should get you started. Also, this will only work if you want a single
menu (vs. the same menu appearing in multiple cells*).

First, show the forms toolbar: View Toolbars Forms

Then get your menu started:

-Click on the "Combo Box" icon (mouse over the new toolbar until this
comes up)
-Click and drag (just like a text box) into your workbook
-Right click the menu and choose "Format Control"
-For the "input range" select the data in the workbook you want
to appear in the list
-For the output range, choose a cell in the existing workbook you
can reference
-Specify the number of rows you want to appear in the list
-Then Okay out of it

When you select from the menu, the output range will change to a
numeric (that can be used in conjunction with a vlookup against the
list if you need to translate to some other value).

*If you want several cells to contain the list, you might want to find
a copy to copy the list from your other workbook into the active one
(into hidden cells?) and then stick with the validation option you
originally tried, but within the same book now.

At least that's a couple of ideas anyway.



Jim


Jeff Bendert wrote:
How would I do that?

"jim" wrote:

Not sure about using data validation for this, but you could use a
combo box to select from a list that lives in a different workbook.



Jim




Jeff Bendert wrote:
Excel 2003

Hello,

I am trying to create a drop-down list in a workbook based on a range of
cells in another workbook via the Data, Validation menu commands. I have
followed the steps as specified in the Help system which state that I can
reference a "different worksheet in a different workbook." However when I
try to create the list I receive the message that you cannot use external
references (I am using a named range created in the workbook that references
the external cells).

Thanks for any help you can provide.