drop-down list error

[silvanet]

I followed the Excel Help directions to the letter creating a drop-down list with an outside spreadsheet reference, but when I went to finish, I got a pop-up error message saying "You may not use references to other worksheets or workbooks for Data Validation criteria." What's that all about? The instructions clearly say that you CAN use references to other worksheets or workbooks!

 

[erik.van.geit]

I do not like to spend a lot of time to reproduce your error.
You can send me the thing and I can look at it.

at the same time, I can send you what I have

 

[madhun]

OK, those instructions are missing a step. You need to define a name in the source workbook as well, then refer to that name when creating the name in the workbook where you want to use the validation list. So if you define a name in WorkbookB.xls called ListData that refers to the range you want to use as the list, then in WorkbookA to use this as the Validation source, you need to define a name (say MyList) that refers to =WorkbookB.xls!ListData and you can then use =MyList in the Data Validation source box.

I managed to create the same error, finding this thread to resolve, thanks a million. Is it possible to still have the drop down list with WorkbookB closed?