I have a spreadsheet that I must build anew every week. Each weekly spreadsheet has hundreds of cells containing this formula:

VLOOKUP(C2,[Wage_Lookup_Table.xlsx]Sheet1!$A$5:$C$37,2,FALSE)

As you can see, this formula is referencing the range $A$5:$C$37 in Sheet1 of the remote worksheet Wage_Lookup_Table.xlsx. My problem is that the remote wage is a list of employees containing their "billable rate". That remote table is constantly growing. So I'm constantly having to update references in my weekly spreadsheets.

Is it possible that I could open Wage_Lookup_Table.xlsx and create a named range (e.g. "workertable") then just have the formulas in my weekly billing spreadsheet reference? If that's possible, I don't know how to write the reference, but I assume that the logic would be something like "Wage_Lookup_Table.xlsx"."Sheet1"."workertable". My goal is to make only one update in only one place and have all the dependent spreadsheets update (when opened again). Is that possible? If yes, how?

You can define a dynamic named range in Wage_Lookup_Table.xlsx. Name it for example BillableRates and make it refer to

=OFFSET(Sheet1!$A$5,0,0,COUNTA(Sheet1!$A$5:$A$10000),3)

In your other workbook, you can now use

=VLOOKUP(C2,[Wage_Lookup_Table.xlsx]!BillableRates,2,FALSE)

Please note that the Wage_Lookup_Table.xlsx workbook must be open together with the workbook containing these VLOOKUP formulas. Excel cannot resolve a dynamic range in a closed workbook.