Create an external reference link with embedded variable

[Greentree]

For several years I have used a spreadsheet to balance my checkbook and
provide YTD totals by category for a span of the previous 5 years.
Each year is a separate workbook and each total is accomplished by an
external link to the annual worksheet for that year and the monthly
sheet within that workbook An example would be ='C:\Documents and
Settings\Greentree\My Documents\[CheckLog 2004.xls]January'!$L8. My
problem is that it becomes fairly labor intensive to roll all of these
formulas forward each year. It would be much easier if I could replace
the file name year (in this case CheckLog *2004*.xls) with a variable
such as =TEXT(YEAR($A$1),"####")-1, where year is current year. I have
experimented on several occasions but can't seem to get the punctuation
right to make it work. I would be most grateful to anyone who can
provide some assistance........

 

[Bernard Liengme]

Not sure if this will help. but here are three formulas that give the same
result when C2 holds the entry 1998
=[Book1998.xls]Sheet1!$F$1
=INDIRECT("'C:\Documents and Settings\Owner\My
Documents\Trials\[Book"&C2&".xls]Sheet1'!$F$1")
=INDIRECT("'[Book"&C2&".xls]Sheet1'!$F$1")

The last two can be copied down the column to reference other books if C3,
C4, etc hold other values. If you want to copy across a different cell in
the book, use =INDIRECT("'[Book"&$C2&".xls]Sheet1'!F$1")

Note that if the file is not open you get the #REF! error
Some VBA to open all the files might help