Using cell value to reference external worksheet?
Hi
Try
=VLOOKUP($C$2,INDIRECT("G:\Excel\[MILLAGES.xls]"&$A$16&"!$A$2:$E$37"),3)
--
Regards
Roger Govier
"tsobiech" wrote in message
...
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
|