Hi. I'm doing a bit of work for a bookkeeper. They have two spreadsheets, one referencing another. I would like to reference the 2nd spreadsheet using a constant variable containing the path of that 2nd spreadsheet rather than explicitly declare the path in every cell. That way the path can be updated en-masse.

Here's the original formula:
=IF(ISNA(VLOOKUP($A5,'C:\Public\Documents\Finance\Credit Control\[Order Book And Credit Control.xlsx]Order_Book'!$A$2:$N$99,12,FALSE)),"NOT RELEVANT",VLOOKUP($A5,'C:\Public\Documents\Finance\Credit Control\[Order Book And Credit Control.xlsx]Order_Book'!$A$2:$N$99,12,FALSE))
I hit CTRL-F3 and created a named constant pointing to a path string on a 2nd worksheet. The constant is called cc and the string looks like this:
'C:\Public\Documents\Finance\Credit Control\[Order Book And Credit Control.xlsx]Order_Book'
I then adjusted the formula above by enterning cc in place of the first path:
=IF(ISNA(VLOOKUP($A5,cc!$A$2:$N$99,12,FALSE)),"NOT RELEVANT",VLOOKUP($A5,'C:\Public\Documents\Finance\Credit Control\[Order Book And Credit Control.xlsx]Order_Book'!$A$2:$N$99,12,FALSE))
The problem being, when I hit enter on that formula, Excel opens up the dialog box to pick the file. Any ideas anyone?

Thanks in advance,
Nick