Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a row to worksheet does not update cell references in another. | Excel Worksheet Functions | |||
Reference External Worksheets & Protect Worksheet / Workbook | Excel Worksheet Functions | |||
How to change reference to other worksheet by changing one cell? | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Cell Reference Math | Excel Worksheet Functions |