Worksheet reference in validation

[WLMPilot]

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

 

[FSt1]

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

 

[Debra Dalgleish]

For the data validation source, use: =Employees

without any reference to the sheet name.

 

[Don]

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

 

[T. Valko]

Watch this 5 min video: