Use a named range in formula referencing data in another workbook

[KGee]

I have a workbook with thousands and thousands of lookups and references to data in other workbooks. Most of the formulas have one small piece that needs to be updated each time we create a new workbook. I wanted to use named ranges where those values could be referenced in the formula to see if that would work better than doing a find/replace which takes forever. Below is a typical path with a hard coded reference. The value for "YYY" is something I wanted to pull from a named range. If I insert the named range and wrap the path in double quotes it's treated like a text string but I thought it could be done using multiple quotes or some type of escape character.

='\\server\share$\folder1\folder2\folder3\folder4\[xxxxxx xxx YYY xxxxxxx.xlsx]sheet name'!$H$5

='\\server\share$\folder1\folder2\folder3\folder4\[xxxxxx xxx MYNAMEDRANGE xxxxxxx.xlsx]sheet name'!$H$5

 

[jasonb75]

If I insert the named range and wrap the path in double quotes it's treated like a text string

That's because it is a text string, to make it a valid range you would need to wrap the entire string with the INDIRECT function.

=INDIRECT("'\\server\share$\folder1\folder2\folder3\folder4\[xxxxxx xxx "&YYY&" xxxxxxx.xlsx]sheet name'!H5")

$ symbols are not needed inside the double quotes, all ranges formed as part of a text string are absolute.

Note that using this method makes the function volatile, which can be counter productive.

Volatile Excel Functions -Decision Models

Microsoft Excel Calculation Secrets and optimisation tips, calculation methods, calculation sequence, dependencies and memory limits.

www.decisionmodels.com

 

[KGee]

That's because it is a text string, to make it a valid range you would need to wrap the entire string with the INDIRECT function.

=INDIRECT("'\\server\share$\folder1\folder2\folder3\folder4\[xxxxxx xxx "&YYY&" xxxxxxx.xlsx]sheet name'!H5")

$ symbols are not needed inside the double quotes, all ranges formed as part of a text string are absolute.

Note that using this method makes the function volatile, which can be counter productive.

Volatile Excel Functions -Decision Models

Microsoft Excel Calculation Secrets and optimisation tips, calculation methods, calculation sequence, dependencies and memory limits.

www.decisionmodels.com

Thanks for responding. I keep getting a "#REF!" error but it seems like I have the right syntax. Could spaces in the file name or spaces in the sheet name cause any issues?

 

[jasonb75]

Sorry, something I forgot to add, the workbook(s) referred to as YYY must be open.

 

[KGee]

Just figured that out . Any way to get around that? All of these files are rather large and slow down my laptop.

 

[jasonb75]

Not really, there is an old add in called MoreFunc which has a function that works without you opening the files, but this is achieved by some background code opening the files hidden from view so it will still have the same processing overheads, if not more.

The alternative is the one that you're trying to get away from, find and replace with a regular formula. Have you tried setting calculation to manual while you do the find and replace?
If you have only have to change YYY in every cell then it will probably not make any difference, but if you have to change XXX, YYY and ZZZ then you could potentially save 2/3 of the recalc time.