Linking Data Validation Lists to a Workbook

Hi,

I have a question regarding linking the content for data validation lists stored in a different workbook.

The scenario is as follows:
I will have say 50 users using a template which contains various dependent drop-down lists. These lists work by using the INDIRECT function which calls the
correct drop-down based on the previous selection. The drop-down entries are currently stored in named ranges on a hidden worksheet in each user's file.

However, if I need to make a change to the content of these lists, I will have to open all 50 files individually and change each one. So I'd like to have a master file to which all the templates would be linked which would contain the entries for each drop-down. So when I update a list in the Master file, it would automatically update the drop-down lists in each user's template with any additions deletions.

My question is, how do I create the links between the user templates and the master and have it so that the list ranges will be dynamic. So if a drop-down list contains say 10 entries and I add 2 more in the master, that those 12 now appear in each user's template?

I'd greatly appreciate any ideas on how I could approach this.

I believe you'll find something here, video also :

http://www.contextures.com/xlDataVal05.html

Regards, Luka

Hi Luka,

Many thanks for your reply.

I had seen this solution but it requires the 'Master' workbook to be open, which I don't want.

Is there any way it could be achieved with the Master workbook closed?

Thanks.

I'm not sure, but probably It could be done with VBA, just don't know how. Sorry, I can't help you on this one.