Data validation
 

sHello,
I have excel 2003. I also have to 2 worksheets that look up the same list in
data validation. It used to work in prior version, but I get the message "May
not use reference to other workbooks or worksheets for Data Validation
criteria. Is there any way around this. Any help will be greatly appreciated.
--
thank you mac

Hi Mac

For all versions of Excel, you have to name the list
(select list and enter name in "NameBox" (to the left
of the formula bar)). Use this name in the validation box.

The reason is, that Excel needs a *global* (not local) range
for a validation list.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"mac" skrev i en meddelelse
...
sHello,
I have excel 2003. I also have to 2 worksheets that look up the same list
in
data validation. It used to work in prior version, but I get the message
"May
not use reference to other workbooks or worksheets for Data Validation
criteria. Is there any way around this. Any help will be greatly
appreciated.
--
thank you mac

Hi!

Use a defined name to reference the source of your
dropdown data.

If the source of your dropdown is in sheet2 A1:A10, goto
InsertNameDefine. Enter a name then in the Refers to box
enter =Sheet2!$A$1:$A$10. OK out.

To use this as the source for a dropdown on any other
sheet:

Goto DataValidation
Allow: List
Source: =Mylist (or whatever name you choose)

Biff

-----Original Message-----
sHello,
I have excel 2003. I also have to 2 worksheets that look
up the same list in
data validation. It used to work in prior version, but I
get the message "May
not use reference to other workbooks or worksheets for
Data Validation
criteria. Is there any way around this. Any help will be
greatly appreciated.
--
thank you mac
.