software

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 quot;targetquot; 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 quot;keeps itself up
to date,quot; rather than me having to do it manually?

I hope I have explained this clearly enough to be understood

Thanks for your assistance,
Tom

Hi

Try
=VLOOKUP($C$2,INDIRECT(quot;G:\Excel\[MILLAGES.xls]quot;amp;$A$16amp;quot;!$A$2:$E$37quot;),3)--
Regards

Roger Govierquot;tsobiechquot; gt; wrote in message
...
gt; Hello,
gt;
gt; I am curious if this is possible, and I'm having difficulty locating
gt; any
gt; info....
gt;
gt; I have formulas which reference data from an external workbook that
gt; has
gt; different worksheets for each year...
gt;
gt; =VLOOKUP($C$2,'G:\Excel\[MILLAGES.xls]2006'!$A$2:$E$37,3)
gt; =VLOOKUP($C$2,'G:\Excel\[MILLAGES.xls]2005'!$A$2:$E$37,3)
gt;
gt; etc....
gt;
gt; The external workbook gets a new worksheet added each year, so there
gt; is
gt; always an existing quot;targetquot; for the formula to find.
gt; However, each year I have to update these forumlas to reflect the
gt; current
gt; year, which is time consuming and prone to errors.
gt; Since there is a cell at the beginning of each row that has the
gt; desired year
gt; in it...is there any way to use that cell value as a reference for the
gt; external data sheet?
gt;
gt; Theoretically (I know this won't work because I already tried it)
gt; speaking...
gt;
gt; =VLOOKUP($C$2,'G:\Excel\[MILLAGES.xls]A16'!$A$2:$E$37,3)
gt;
gt; so that if cell A16 = 2006, then the VLOOKUP formula looks for the
gt; external
gt; worksheet 'G:\Excel\[MILLAGES.xls]2006'
gt; and if cell A16 = 2005, then the VLOOKUP formula looks for
gt; 'G:\Excel\[MILLAGES.xls]2005'
gt;
gt; Is there any way to accomplish this, so that the worksheet quot;keeps
gt; itself up
gt; to date,quot; rather than me having to do it manually?
gt;
gt; I hope I have explained this clearly enough to be understood
gt;
gt; Thanks for your assistance,
gt; Tom
You need the INDIRECT() function

=VLOOKUP($C$2,INDIRECT(quot;'G:\Excel\[MILLAGES.xls]quot;amp;A16amp;quot;'!$A$2:$E$37quot;),3)

BE AWA 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 OPENquot;tsobiechquot; wrote:

gt; Hello,
gt;
gt; I am curious if this is possible, and I'm having difficulty locating any
gt; info....
gt;
gt; I have formulas which reference data from an external workbook that has
gt; different worksheets for each year...
gt;
gt; =VLOOKUP($C$2,'G:\Excel\[MILLAGES.xls]2006'!$A$2:$E$37,3)
gt; =VLOOKUP($C$2,'G:\Excel\[MILLAGES.xls]2005'!$A$2:$E$37,3)
gt;
gt; etc....
gt;
gt; The external workbook gets a new worksheet added each year, so there is
gt; always an existing quot;targetquot; for the formula to find.
gt; However, each year I have to update these forumlas to reflect the current
gt; year, which is time consuming and prone to errors.
gt; Since there is a cell at the beginning of each row that has the desired year
gt; in it...is there any way to use that cell value as a reference for the
gt; external data sheet?
gt;
gt; Theoretically (I know this won't work because I already tried it) speaking...
gt;
gt; =VLOOKUP($C$2,'G:\Excel\[MILLAGES.xls]A16'!$A$2:$E$37,3)
gt;
gt; so that if cell A16 = 2006, then the VLOOKUP formula looks for the external
gt; worksheet 'G:\Excel\[MILLAGES.xls]2006'
gt; and if cell A16 = 2005, then the VLOOKUP formula looks for
gt; 'G:\Excel\[MILLAGES.xls]2005'
gt;
gt; Is there any way to accomplish this, so that the worksheet quot;keeps itself up
gt; to date,quot; rather than me having to do it manually?
gt;
gt; I hope I have explained this clearly enough to be understood
gt;
gt; Thanks for your assistance,
gt; Tom