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 he
http://www.download.com/Morefunc/300...-10423159.html

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

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

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JBeaucaire" wrote:


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
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64559