I have a doc where I use this formula in the spreadsheet:
where SS_Ref is defined as: ="[Customer_Data.xlsx]User_Data!A:D"Code:=VLOOKUP($J$6,INDIRECT(SS_Ref),2,FALSE)
where I have the Customer_Data auto open when Excel is launched and is a hidden workbook - so once installed the user never sees it
I tried a variety of Indirect etc with the Customer_Data in a cell on the page and couldn't quote seem to get that to work.
Your example might look something like this:
where you created a named range "External_Ref" and define that range as:Code:=VLOOKUP($A18,INDIRECT(External_ref),AS$5+1,FALSE)
"'C:\Work\program worksheets\[AD Mktg-Com 2012 R2.xlsb]2012'!costcenter_PGE"
although if multiple users need to either make sure that source is always in the same place.
You could write a macro to check that the doc is in the correct place or update the named range given a user supplied file name and location.
I went with the hidden workbook as it was simpler for multiple users across Mac and Windows versions of Excel - and allows me to control who as the customer data file installed on their system.