Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Referencing multiple worksheets
I have a workbook with a summary sheet for each year from 2004 through 2010
and twelve worksheets named Jan, Feb Mar etc. At the end of each month, data from another program is downloaded into the appropriate month. I then use Vlookup to find the data for each reference number used in the summary and monthly worksheets and insert it in the summary under the correct month and against the correct customer in the list. I want to be able to use the column headings on the summary sheet (Jan, Feb, Mar etc) to build the Vlookup function worksheet reference. For example I use Vlookup(A2,Jan!$a$2:$c$79,3,false) .This is copied and edited across the twelve months and I currently search and replace to change the original "Jan" part of the worksheet reference to the appropriate months. I thought I should be able to copy this formula using the relative reference to the column headings to build the correct formula for each column; Something like "=vlookup(A2, (C1&!&$a$2...." How can I implement this. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Referencing multiple worksheets
Hi Ed
You need to incorporate the Indirect function =Vlookup(A2,Indirect(C1&"!$a$2:$c$79"),3,false) Because the range A2:C79 is held within quotes, and will not alter as you copy the formula, you code make the formula look a bit "cleaner" by omitting the $ signs =VLOOKUP(A2,INDIRECT(C1&"!A2:C79"),3,0) -- Regards Roger Govier "EdGarrett" wrote in message ... I have a workbook with a summary sheet for each year from 2004 through 2010 and twelve worksheets named Jan, Feb Mar etc. At the end of each month, data from another program is downloaded into the appropriate month. I then use Vlookup to find the data for each reference number used in the summary and monthly worksheets and insert it in the summary under the correct month and against the correct customer in the list. I want to be able to use the column headings on the summary sheet (Jan, Feb, Mar etc) to build the Vlookup function worksheet reference. For example I use Vlookup(A2,Jan!$a$2:$c$79,3,false) .This is copied and edited across the twelve months and I currently search and replace to change the original "Jan" part of the worksheet reference to the appropriate months. I thought I should be able to copy this formula using the relative reference to the column headings to build the correct formula for each column; Something like "=vlookup(A2, (C1&!&$a$2...." How can I implement this. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Referencing multiple worksheets
Thank you Roger, you are a legend!
"Roger Govier" wrote: Hi Ed You need to incorporate the Indirect function =Vlookup(A2,Indirect(C1&"!$a$2:$c$79"),3,false) Because the range A2:C79 is held within quotes, and will not alter as you copy the formula, you code make the formula look a bit "cleaner" by omitting the $ signs =VLOOKUP(A2,INDIRECT(C1&"!A2:C79"),3,0) -- Regards Roger Govier "EdGarrett" wrote in message ... I have a workbook with a summary sheet for each year from 2004 through 2010 and twelve worksheets named Jan, Feb Mar etc. At the end of each month, data from another program is downloaded into the appropriate month. I then use Vlookup to find the data for each reference number used in the summary and monthly worksheets and insert it in the summary under the correct month and against the correct customer in the list. I want to be able to use the column headings on the summary sheet (Jan, Feb, Mar etc) to build the Vlookup function worksheet reference. For example I use Vlookup(A2,Jan!$a$2:$c$79,3,false) .This is copied and edited across the twelve months and I currently search and replace to change the original "Jan" part of the worksheet reference to the appropriate months. I thought I should be able to copy this formula using the relative reference to the column headings to build the correct formula for each column; Something like "=vlookup(A2, (C1&!&$a$2...." How can I implement this. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Referencing multiple worksheets
And just in case that value in C1 is a name of a worksheet that would require
apostrophes surrounding it: =VLOOKUP(A2,INDIRECT("'" & C1 & "'!A2:C79"),3,0) Roger Govier wrote: Hi Ed You need to incorporate the Indirect function =Vlookup(A2,Indirect(C1&"!$a$2:$c$79"),3,false) Because the range A2:C79 is held within quotes, and will not alter as you copy the formula, you code make the formula look a bit "cleaner" by omitting the $ signs =VLOOKUP(A2,INDIRECT(C1&"!A2:C79"),3,0) -- Regards Roger Govier "EdGarrett" wrote in message ... I have a workbook with a summary sheet for each year from 2004 through 2010 and twelve worksheets named Jan, Feb Mar etc. At the end of each month, data from another program is downloaded into the appropriate month. I then use Vlookup to find the data for each reference number used in the summary and monthly worksheets and insert it in the summary under the correct month and against the correct customer in the list. I want to be able to use the column headings on the summary sheet (Jan, Feb, Mar etc) to build the Vlookup function worksheet reference. For example I use Vlookup(A2,Jan!$a$2:$c$79,3,false) .This is copied and edited across the twelve months and I currently search and replace to change the original "Jan" part of the worksheet reference to the appropriate months. I thought I should be able to copy this formula using the relative reference to the column headings to build the correct formula for each column; Something like "=vlookup(A2, (C1&!&$a$2...." How can I implement this. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro referencing multiple worksheets | Excel Discussion (Misc queries) | |||
Formula Referencing data on multiple worksheets | Excel Worksheet Functions | |||
Sort rows across multiple worksheets - Excel 2003 | Excel Worksheet Functions | |||
Excel 2003 Referencing multiple workbooks via single variable | Excel Worksheet Functions | |||
Summary worksheet referencing multiple worksheets | Excel Worksheet Functions |