PDA

View Full Version : Data Validation list referencing



sconly
05-21-2010, 08:54 AM
Is it possible to reference a range in workbook1 and then use it as a validation list in workbook2?

Thanks

p45cal
05-21-2010, 10:44 AM
When I tried I got:
"You cannot use referencs to other worksheets or workbooks for Data Validation criteria"

Now this is not quite true, you can use references to data on other sheets if they're named ranges. Of course, we're trying other workbooks.
I was trying the likes of:
='C:\Documents and Settings\someone\My Documents\Vlidation.xlsx'!ValList
in the Source field.

A workround is to have that formula in a sheet somewhere in a range the same size (array entered), name it and use that in the source field:
=AnotherList

which worked grabbing the info from a closed workbook.

mikerickson
05-22-2010, 12:29 AM
I selected a cell in Workbook2.xls and defined a name

Name: myList
RefersTo: [Workbook1.xls]Sheet1!$A$1:$A$10

and then set validation on a cell in Workbook2 with a list source of =myList

Whenever Workbook1.xls was open, the validation list was as expected.