Data validation

[mac]

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.

 

[Leo Heuser]

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.

 

[Biff]

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
Insert>Name>Define. 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 Data>Validation
Allow: List
Source: =Mylist (or whatever name you choose)

Biff