![]() |
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 |
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 |
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 |
'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 |
All times are GMT +1. The time now is 07:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com