advanced reference question reworded

[Chad]

Ok I have a cell that references a workbook titled June Sales.xls and one
cell that references July Sales.xls Is there a way to write a macro that
would check all formulas for any month and replaces it with the current
month. So that if I ran the macro today both the cells above would end up
referencing February Sales.xls?

 

[JBeaucaire]

You can actually do this without VBA. Simplest way is to have a cell on
your sheet that is set to display the long name of the current month.
Let's pick cell AA1 and put in this formula:

=TODAY()

...and format that cell with the custom number format MMMM. This will
say February now, since that's the current month.

Now, if your original formula to grab cell B2 from June Sales.xls was
this:

='[June Sales.xls]Sheet1'!$B$2

...you can change it to this and it will change "June" to "February"
dynamically:

=INDIRECT("'["&$AA$1&" Sales.xls]Sheet1'!$B$2")

 

[JBeaucaire]

One thing to remember, though, INDIRECT doesn't work with closed workbooks.
They will need to be open.

If you must work with closed workbooks, there is an addin called MOREFUNC
and it includes a replacement function called INDIRECT.EXT() that works on
closed workbooks.

You can download it from here:
http://www.download.com/Morefunc/3000-2077_4-10423159.html

Install it, then click on TOOLS > ADDINS and activate MOREFUNC.

You can read up on using the various functions here:
http://xcell05.free.fr/morefunc/english/#Functions