drop down menu

[massi]

I am trying to create a drop down list.
i have a spreadsheet with the list of the values i want in the drop down
menu in an other workbook
i have followed the instruction of:
http://office.microsoft.com/en-us/excel/HP052022151033.aspx
but when i try to insert the drop down list i get an error msg.
in the final work book i went to Data/Validation, i have select List from
allow window but when i try to define the actual list i need from an another
workbook the message that appears is:
"you may not use references to other worksheets or workbooks for Data
Validation criteria"
what do i do wrong?
thanks in advance

 

[Michael]

Change the type of dropdown. Must likely you selected your
dropdown from the Forms Toolbar, You must now create your dropdown using the
Control Toolbox Toolbar(click on the View Option from your Main Menu to
select).
Once You insert your combobox(dropdown), right click on it and select
properties,
Click on the "Categorized" tab and under the Miscellaneous Category place
your data range cell reference on the ListFillRange (i.e. Sheet1!A1:A50)
This allows to use a different worksheet than the one currently in use.

 

[massi]

i'm lost...
the drop down list i used was from the main menu:
Data / Validation etc...

where do i get the view option from the main menu?

 

[Michael]

I see. . .
Then what you have to do is:
From the Main Toolbar select Insert->Name->Define
Once you have the dialog box, Type any name on it like mylist or something
and then
on the refers to box click on the icon with the red arrow and go to the
sheet where your range is and highlight the range or simply type the
reference like so:

=Sheet2!$H$3:$H$8
Hit Ok
Now, when you do your data validation, on the settings tab select from the
Allow dropdown: List
and on the source type =mylist
Or whatever you name you range on the first step. That should do the trick.
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

 

[massi]

it's the second step that i cannot do. i could define the list but it's the
validation that gives me troubles.
how do i have to do? so far i did from the main menu but i guess it's not
there...
thanx


I see. . .
Then what you have to do is:
From the Main Toolbar select Insert->Name->Define
Once you have the dialog box, Type any name on it like mylist or something
and then
on the refers to box click on the icon with the red arrow and go to the
sheet where your range is and highlight the range or simply type the
reference like so:

=Sheet2!$H$3:$H$8
Hit Ok
Now, when you do your data validation, on the settings tab select from the
Allow dropdown: List
and on the source type =mylist
Or whatever you name you range on the first step. That should do the trick.
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

 

[Michael]

Please explain the process you are following, there must be something I am
missing, because the Defining of the name range and the applying of it your
validation should work.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

 

[Michael]

From the MainToolbar select:

Data->Validation
From the Settings tab Click on the Allow dropdown and select List
On the Source box type
=thenameyoudefined
Dontforget the equal

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

 

[massi]

i have done that already but it doesn't work.
i use excel 2003 is this the reason?
i get the message that the source currently evaluates to an error. (which is
already an improvement because before it wasn't allowing me to refer to
MyList...)

 

[massi]

Hi Michael,
at the end i manage to fix the thing.
i have managed to set up the list in an other sheet of the same work book
which takes the values via link to the other workbook. at the end the result
is ok. i don't know why at first excel didn't let me set up the list in a
different sheet...
anyway, thanks for your help.
Rgds
Massimo

 

[Michael]

I think I missed the fact that it was in a different workbook, I thought it
was only on a different worksheet, but yes your approach should be the answer.
I'm glad I was able to point you in the right direction.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.