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.

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.

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.