I am using several spreadsheets at work. One is a list of activity for each day, and the other is a set of statements for each customer. The latter references the activity spreadsheet, along with one or two other pricing spreadsheets.

When I create a new spreadsheet for each month, I have to update the formulas to now reference the proper source for the formulas. I have to remember every location that is using a formula that needs updated in this manner. I'm wondering if there's a way to do it using some kind of variable. I'm not terribly familiar with Excel's Name system, so maybe that would work?

For example, here is one formula that would need updated:

=VLOOKUP($F30, 'G:\Accounting\_Acct\Facility\Allocations\2016 Allocations\10-2016\[10-2016 Monthly Allocation & Fee Calculation.xlsx]Fee Summary'!$A$70:$H$78, 6, 0)

When I create November's, I will have to do Find and Replace 10-2016 with 11-2016 (twice), and when I create January's in a few months I'll have to replace the 2016 with 2017.

Is there a way to set these formulas so that I can replace the file name I'm referencing with a variable that I could simply make one change to? Something like this:


=VLOOKUP($F30, 'G:\Accounting\_Acct\Facility\Allocations\"YYYY" Allocations\"MM-YYYY"\["MM-YYYY" Monthly Allocation & Fee Calculation.xlsx]Fee Summary'!$A$70:$H$78, 6, 0)