If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Copying the same cell from different sheets
I have a bunch of worksheets, each formatted identically. Let's say their
names are "A" to "Z". I insert a new sheet and I want to examine the same cell from all sheets. Suppose there is a value representing annual expenses in cell D13 of each sheet. My new sheet might look something like this: Sheet Annual Expense A $100 B $150 C $200 etc. Is there a way to do this without having to enter the formula separately into each cell? It seems to me I saw somewhere a way to "build" a formula by stringing the components together. |
#2
|
|||
|
|||
Hi steve
Try this to create the formulas with a macro http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Steve" wrote in message ... I have a bunch of worksheets, each formatted identically. Let's say their names are "A" to "Z". I insert a new sheet and I want to examine the same cell from all sheets. Suppose there is a value representing annual expenses in cell D13 of each sheet. My new sheet might look something like this: Sheet Annual Expense A $100 B $150 C $200 etc. Is there a way to do this without having to enter the formula separately into each cell? It seems to me I saw somewhere a way to "build" a formula by stringing the components together. |
#3
|
|||
|
|||
IF and only IF your sheets are actually named as displayed, then use the
formula =INDIRECT(cell_containing_first_sheet_name&"!d13") then copy it down for all the sheet names If D13 is not a constant - i.e., you want to use different cell addresses in different summaries, then put the address (D13) in another cell, say B1, and use this formula =INDIRECT(cell_containing_first_sheet_name&"!"&B$1 ) "Steve" wrote: I have a bunch of worksheets, each formatted identically. Let's say their names are "A" to "Z". I insert a new sheet and I want to examine the same cell from all sheets. Suppose there is a value representing annual expenses in cell D13 of each sheet. My new sheet might look something like this: Sheet Annual Expense A $100 B $150 C $200 etc. Is there a way to do this without having to enter the formula separately into each cell? It seems to me I saw somewhere a way to "build" a formula by stringing the components together. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
GET.CELL | Biff | Worksheet Functions | 2 | November 24th, 2004 07:16 PM |
copying and controling a formula cell to cell | kevin | Worksheet Functions | 5 | February 13th, 2004 01:26 PM |
how to chart same cell in multiple sheets | bn2wild | Charts and Charting | 1 | January 12th, 2004 05:40 PM |