Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,480
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro referencing multiple worksheets JULZ Excel Discussion (Misc queries) 1 October 9th 06 07:46 PM
Formula Referencing data on multiple worksheets ChrisPrather Excel Worksheet Functions 6 August 22nd 06 05:14 AM
Sort rows across multiple worksheets - Excel 2003 Stamdale Excel Worksheet Functions 2 July 5th 05 04:30 PM
Excel 2003 Referencing multiple workbooks via single variable BBohannon Excel Worksheet Functions 0 April 20th 05 08:32 PM
Summary worksheet referencing multiple worksheets Jon Excel Worksheet Functions 1 January 27th 05 02:12 AM


All times are GMT +1. The time now is 06:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"