Excel 2003 Referencing multiple worksheets

[EdGarrett]

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.

 

[Roger Govier]

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)

 

[EdGarrett]

Thank you Roger, you are a legend!

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)

 

[Dave Peterson]

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)