Template Duplication Formula - Please help

Hi There,

I'm hoping someone can help me. I have two workbooks; one which is a master workbook and the other which I'm going to duplicate a number of times based on a template.

The template has the following formula:

='[AVAILABILITY SPREADSHEET MASTER.xlsx]Devon'!X4

"Devon" represents one of the tabs in the master workbook which also has many other tabs with different names such as "Apollo" and my current challenge is that every time I duplicate my template, I then need to manually change "Devon" to "Apollo" and so on.

I would like to be able to have 1 cell in the template sheet called "Devon" which I can change to "Apollo" which would then change all other formulas such as the above that have "Devon" in it. The cell reference in the template sheet to change would be U1.

Thank you for your help,

David

You would normally use the INDIRECT function to do this.

However, that function does not work with closed workbooks, so you would have to ensure that the Master workbook is open, as well as the duplicated file. Is that something you could guarantee to happen?

Hope this helps.

Pete