Where are these instructions? You cannot use references to other sheets unless you use a defined name in the activeworkbook and then refer to that defined name in the data validation dialog. (might be different in Excel 2007, I can't recall offhand).
Click on Help, search on "Create a drop-down list from a range of cells"... it reads
"If you want to use another worksheet or another workbook, do one of the following:
1.
Use a different worksheet in the same workbook ..."
which was NOT what I wanted to do.
It goes on,
"2.
Use a different worksheet in a different workbook Type the list on that worksheet, and then define a name with an external reference to the list."
[now, that is what I wanted to do, and the instructions imply clearly that you can do so]
How?
"Open the workbook that contains the list of drop-down entries.
Open the workbook where you want to validate cells, point to Name on the Insert menu, and then click Define.
In the Names in workbook box, type the name, for example, ValidDepts.
Accept the default value in the Refers to: box, and then click OK.
In the Refers to box, delete the contents, and keep the insertion pointer in the box.
On the Window menu, click the name of the workbook that contains the list of drop-down entries, and then click the worksheet that contains the list.
Select the cells containing the list.
In the Define Name dialog box, click Add, and then click Close. "
If you follow the instructions, you will see that it does not work. I'm using Excel 2003, SP3.
The error you will get is exactly as I mentioned.