Using cell value to reference external worksheet?
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
|