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 |
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 |
Using cell value to reference external worksheet?
You need the INDIRECT() function
=VLOOKUP($C$2,INDIRECT("'G:\Excel\[MILLAGES.xls]"&A16&"'!$A$2:$E$37"),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 OPEN "tsobiech" wrote: 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 |
All times are GMT +1. The time now is 09:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com