Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Kim
 
Posts: n/a
Default Linking References from Multiple Sheets to One Summary Sheet

I have a workbook that contains several work sheets with very similar
information. In each of these sheets, I need to make a reference from that
sheet to a summary sheet. I need entries on the summary sheet for each of
the other worksheets. The cell references for each sheet will be the same.
For example, the cell a4 in the sheet January will be referenced in the
summary. a4 will also be referenced in the summary from the sheet February,
etc.

Is there a quick way to reference all of the other sheets in the summary
without editing the linking formula in the summary sheet? (e.g.
=January!$A$1 changed to =February!$A$1 )

Thank you!

Kim
  #2   Report Post  
Gord Dibben
 
Posts: n/a
Default

Kim

In an out of the way column in your summary sheet type a list of your sheet
names.

Then you can use the INDIRECT Function in your formulas.

i.e. list of sheets in AA1:AA12

In A1 enter =INDIRECT(AA1 & "!" & "$A$4")

Drag/copy down to A12

If you want the sheet names on a separate sheet, just preface the AA1 with the
sheet name.

=INDIRECT('Name Sheet'!AA1 & "!" & "$A$4")


Gord Dibben Excel MVP


On Wed, 4 May 2005 13:43:36 -0700, Kim wrote:

I have a workbook that contains several work sheets with very similar
information. In each of these sheets, I need to make a reference from that
sheet to a summary sheet. I need entries on the summary sheet for each of
the other worksheets. The cell references for each sheet will be the same.
For example, the cell a4 in the sheet January will be referenced in the
summary. a4 will also be referenced in the summary from the sheet February,
etc.

Is there a quick way to reference all of the other sheets in the summary
without editing the linking formula in the summary sheet? (e.g.
=January!$A$1 changed to =February!$A$1 )

Thank you!

Kim


  #3   Report Post  
Kim
 
Posts: n/a
Default

Thank you very much for your reponse. It is VERY helpful. I noticed that
INDIRECT did not work for sheets that have two word names (e.g. Las Vegas).
Is there a work around to this other than using an underscore between words
(e.g. Las_Vegas)?

Kim

"Gord Dibben" wrote:

Kim

In an out of the way column in your summary sheet type a list of your sheet
names.

Then you can use the INDIRECT Function in your formulas.

i.e. list of sheets in AA1:AA12

In A1 enter =INDIRECT(AA1 & "!" & "$A$4")

Drag/copy down to A12

If you want the sheet names on a separate sheet, just preface the AA1 with the
sheet name.

=INDIRECT('Name Sheet'!AA1 & "!" & "$A$4")


Gord Dibben Excel MVP


On Wed, 4 May 2005 13:43:36 -0700, Kim wrote:

I have a workbook that contains several work sheets with very similar
information. In each of these sheets, I need to make a reference from that
sheet to a summary sheet. I need entries on the summary sheet for each of
the other worksheets. The cell references for each sheet will be the same.
For example, the cell a4 in the sheet January will be referenced in the
summary. a4 will also be referenced in the summary from the sheet February,
etc.

Is there a quick way to reference all of the other sheets in the summary
without editing the linking formula in the summary sheet? (e.g.
=January!$A$1 changed to =February!$A$1 )

Thank you!

Kim



  #4   Report Post  
David McRitchie
 
Posts: n/a
Default

'Las Vegas'

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Kim" wrote in message ...
Thank you very much for your reponse. It is VERY helpful. I noticed that
INDIRECT did not work for sheets that have two word names (e.g. Las Vegas).
Is there a work around to this other than using an underscore between words
(e.g. Las_Vegas)?

Kim

"Gord Dibben" wrote:

Kim

In an out of the way column in your summary sheet type a list of your sheet
names.

Then you can use the INDIRECT Function in your formulas.

i.e. list of sheets in AA1:AA12

In A1 enter =INDIRECT(AA1 & "!" & "$A$4")

Drag/copy down to A12

If you want the sheet names on a separate sheet, just preface the AA1 with the
sheet name.

=INDIRECT('Name Sheet'!AA1 & "!" & "$A$4")


Gord Dibben Excel MVP


On Wed, 4 May 2005 13:43:36 -0700, Kim wrote:

I have a workbook that contains several work sheets with very similar
information. In each of these sheets, I need to make a reference from that
sheet to a summary sheet. I need entries on the summary sheet for each of
the other worksheets. The cell references for each sheet will be the same.
For example, the cell a4 in the sheet January will be referenced in the
summary. a4 will also be referenced in the summary from the sheet February,
etc.

Is there a quick way to reference all of the other sheets in the summary
without editing the linking formula in the summary sheet? (e.g.
=January!$A$1 changed to =February!$A$1 )

Thank you!

Kim





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
Copying multiple sheets from one book 2 another and undertake spec Pank Mehta Excel Discussion (Misc queries) 14 March 16th 05 05:41 PM
Multiple sheets selected twa14 Excel Discussion (Misc queries) 2 December 21st 04 12:15 PM
Linking 2 Excel Sheets, Anticipating Lines. Matthew Excel Worksheet Functions 1 December 17th 04 02:55 PM
Linking sheets to a summary sheet in workbook gambinijr Excel Discussion (Misc queries) 4 December 16th 04 09:13 PM
linking multiple sheets to a summary sheet greg g Excel Discussion (Misc queries) 1 December 16th 04 08:43 AM


All times are GMT +1. The time now is 10:40 AM.

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"