This works for a different worksheet in the same Excel file. However, when I
tried to follow the Help instructions to create a defined name referring to
cells in a different file, I get 'You may not use references to other
worksheets or workbooks for Data Validation Criteria'.

Any idea what's going on? BOth files are open and are in the same folder.

What I ultimately want to achieve is this, and feel free to tell me that
data validation through a separate file is not the best way, in which case
please inform me a better way:

I am asking a number of people to record their daily data processing
activities as part of a productivity study. Only about 10 people will do
some recording for about two weeks. It's NOT an IT project. I don't want to
prescribe the kind of activities they are doing, but I do want consistent
entry terminologies. Thus I don't want to see 'social security number' and
'SSN' as both data type entries. so I created a drop down list in the 'data
involved' column so they can pick from the drop down list. Each staff will
fill out his/her own spreadsheet as separate files, so that they don't mess
up with other people's entries. To allow them to add new types of data
processed and at the same time disallow redundant new type creations, I
created a single ValidDataInputTYpes.xls spreadsheet and try to have the drop
downlists refer to the list of valid items in that separate file. Thus all
the people have access to the single ValidDataInputTypes.xls and can add new
data types there. If somebody sees 'SSN" already created, then he/she won't
add another Social Security Number there.

With multiple users accessing the same file, I am now getting into the
synchronization issue. Given the low volume of data involved, and no IT
product will come out of this, I can tolerate a few mistakes and I will
simply address them by hand.

Please let me know what's the easiest way to achieve what I want to do. If
data validation through external file is the way to go and Excel Helps says I
can, how come it doesn't work?
"Tom Ogilvy" wrote:

create a defined name/named range refering to your other sheet and use that
in the source

Insert = Name = define

Name: List1
Refers to: =Sheet3!$A$1:$A$10

click the ADD button.

then in the data validation in the list source textbox

=List1

the equal sign is important.

--
Regards,
Tom Ogilvy

"RigasMinho" wrote:

We all know you can create a drop down list in excel where you put in
the values for the source using certain selected cells or you can type
it in yourself.

How do you select different cells on a different sheet?

It just wont let me go to another worksheet when i try changing
worksheets.

Any ideas?