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)

You can replace the reference with
INDIRECT("'G:\Accounting\_Acct\Facility\Allocation s\"&year(A1)&" Allocations\" &text(a1,"m-yyy")&"\["&text(a1,"m-yyy")& "Monthly Allocation & Fee Calculation.xlsx]Fee Summary'!$A$70:$H$78"

where A1 contains the date

( I hope I got that one right, but you've got the idea)
PS disregard the space in the first occurrence of " allocations", it's a forum bug

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post