Data validation in separate workbork - possible?

[DancingGeek]

Hello,

Is it possible to use data validation in a separate workbook? We want to use Workbook 1 as a template that can be edited only once, and have multiple workbooks that reference dropdowns made available in Workbook1. (Over time, the multiple workbooks will exist, but will need to be updated and we would want dropdowns to have current options.)

I can see potential ways to do this with coding, or potentially linking worksheet values in the multiple sheets to the template....maybe? But I was hoping a formula could be used in Data Validation that references a workbook first, then the named cell range.

Any help is greatly appreciated!

Cheers-
geek

 

[SkipVought]

Simple to do. On a separate sheet, link each list range from your various workbooks. Name each list with a range name and then reference the range name in the data > validation list.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

To add to Skip's answer, the workbook containing the list must be open for the dropdown to work. (Edit, links, open source).



Gavin

 

[GlennUK]

Hi Gavin, no, that's not actually right. Skip's answer was to do direct linking ( source not needed to be open ) in a separate sheet, and generate data validation from that sheet.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Gavona]

Hi Glenn, You are right - I should have read Skip's answer properly. It certainly avoids the need to open a second workbook so in almost all circumstances will be better than the one I envisaged.

Just to clarify my solution (which requires the workbook containing the validation list to be open):

One cannot directly specify in the data validation screen to link to a range in another book e.g. =validation.xls!MyRange will return an error.

Instead you have to define a named range in the workbook which in the "refers to" box says =validation.xls!MyRange.



Gavin

 

[DancingGeek]

Thanks Skip, Gavin and Glenn!