I want to create a list via validation that will reference a the employee
list on another worksheet. The list appears in column A of sheet
"Employees". I have also named the list "Employees".

I have tried:

='Employees'!Employees
='Employees'!A:A
=Employees!Employees

I get a message that you cannot reference a worksheet in validation.

Is there a way to do this without using a macro?

Thanks,
Les

hi
validation applies to the sheet meaning like the message said, you can't
reference other sheet with validation. i have never tried but i doubt that a
macro will help. you source need to be on the sheet the validation is on.

Regards
FSt1

"WLMPilot" wrote:

I want to create a list via validation that will reference a the employee
list on another worksheet. The list appears in column A of sheet
"Employees". I have also named the list "Employees".

I have tried:

='Employees'!Employees
='Employees'!A:A
=Employees!Employees

I get a message that you cannot reference a worksheet in validation.

Is there a way to do this without using a macro?

Thanks,
Les

For the data validation source, use: =Employees

without any reference to the sheet name.


WLMPilot wrote:
I want to create a list via validation that will reference a the employee
list on another worksheet. The list appears in column A of sheet
"Employees". I have also named the list "Employees".

I have tried:

='Employees'!Employees
='Employees'!A:A
=Employees!Employees

I get a message that you cannot reference a worksheet in validation.

Is there a way to do this without using a macro?

Thanks,
Les


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

In a workbook, you can name ranges on any sheet by selecting the range and in
the upper left corrner , type the name "Employee". Then on the other sheet,
use the data / validation and select list then "=Employee" in the source.
You will then have a dropdown for the range you named in the other sheet

Note: 1) if your range on the other sheet has names in every cell, then on
the data validation, you will only be able to type those names or choose them
from a dropdown. 2) if you leave one or more blank in your range, then you
can choose from the list or type in one not on the list and 3) someone can
still copy and past ontop of the cell with the data validation

"WLMPilot" wrote:

I want to create a list via validation that will reference a the employee
list on another worksheet. The list appears in column A of sheet
"Employees". I have also named the list "Employees".

I have tried:

='Employees'!Employees
='Employees'!A:A
=Employees!Employees

I get a message that you cannot reference a worksheet in validation.

Is there a way to do this without using a macro?

Thanks,
Les

Watch this 5 min video:

http://youtube.com/watch?v=t2OsWJijrOM


--
Biff
Microsoft Excel MVP


"WLMPilot" wrote in message
...
I want to create a list via validation that will reference a the employee
list on another worksheet. The list appears in column A of sheet
"Employees". I have also named the list "Employees".

I have tried:

='Employees'!Employees
='Employees'!A:A
=Employees!Employees

I get a message that you cannot reference a worksheet in validation.

Is there a way to do this without using a macro?

Thanks,
Les