Creating external references to spreadsheets with changing names

[BS LeBlanc]

I have multiple monthly reports in excel (example: Jan08_ABC.xls) that
reference particular spreadsheets that also change monthly
(Jan08_ABC_cases.xls). I've created a formula that gets the values it needs,
however I'm trying to figure out how to get the formula to change every
month.

For instance, the formula for January is ='[Jan08_ABC_cases.xls]Sheet1'!$B6
I need it to change for each new month.

I created another formula in cell A1 that will automatically update monthly
with the new name of the needed/referenced file, therefore in Feb that cell
reads Feb08_ABC_cases.xls. So, my question is how do I change my initial
formula to incorporate the name change.

These don't work, but hopefully it gives you a good idea of what I'm trying
to do (A1=Feb08_ABC_cases.xls):
='[(TEXT(A1,"")]Sheet1'!$B6
='[A1]Sheet1'!$B6

 

[krcowen]

BS

I think you will need to use an INDIRECT function. Maybe something
like:

=INDIRECT("[feb.xls]Sheet1!$A$1")

or build the month into a cell like

=INDIRECT("["&($B$6&".xls]Sheet1!$A$1"))

where the month is in cell B6

or build the month explicitly in the INDIRECT function like

=INDIRECT("["&(TEXT(MONTH(NOW()),"mmm")&".xls]Sheet1!$A$1"))

Of course you need to customize the exact name of your file, sheet,
and pick the correct cell(s). Also, the brackets, quotes and
explamation points can be a little tricky to get right in these
formulas. At least they are for me.

Good luck.

Ken
Norfolk, Va