Using cell value to reference external worksheet?

[Guest]

Hello,

I am curious if this is possible, and I'm having difficulty locating any
info....

I have formulas which reference data from an external workbook that has
different worksheets for each year...

=VLOOKUP($C$2,'G:\Excel\[MILLAGES.xls]2006'!$A$2:$E$37,3)
=VLOOKUP($C$2,'G:\Excel\[MILLAGES.xls]2005'!$A$2:$E$37,3)

etc....

The external workbook gets a new worksheet added each year, so there is
always an existing "target" for the formula to find.
However, each year I have to update these forumlas to reflect the current
year, which is time consuming and prone to errors.
Since there is a cell at the beginning of each row that has the desired year
in it...is there any way to use that cell value as a reference for the
external data sheet?

Theoretically (I know this won't work because I already tried it) speaking...

=VLOOKUP($C$2,'G:\Excel\[MILLAGES.xls]A16'!$A$2:$E$37,3)

so that if cell A16 = 2006, then the VLOOKUP formula looks for the external
worksheet 'G:\Excel\[MILLAGES.xls]2006'
and if cell A16 = 2005, then the VLOOKUP formula looks for
'G:\Excel\[MILLAGES.xls]2005'

Is there any way to accomplish this, so that the worksheet "keeps itself up
to date," rather than me having to do it manually?

I hope I have explained this clearly enough to be understood

Thanks for your assistance,
Tom

 

[Roger Govier]

Hi

Try
=VLOOKUP($C$2,INDIRECT("G:\Excel\[MILLAGES.xls]"&$A$16&"!$A$2:$E$37"),3)

 

[Guest]

You need the INDIRECT() function

=VLOOKUP($C$2,INDIRECT("'G:\Excel\[MILLAGES.xls]"&A16&"'!$A$2:$E$37"),3)

BE AWARE: The VLOOKUP() _without_ and INDIRECT() will work even if the
Millages.xls file is closed. With the INDIRECT() function you'll get an
error if that workbook is NOT OPEN