Replicating Worksheet References in Formulas
 

How do you replicate a formula on a summary sheet in Excel that takes its
data from individual cells in different worksheets? I know how to replicate
formulas when working with one worksheet but not when the formulas reference
multiple worksheets. However, the cell reference is the same on ALL
worksheets and it is the NAME of the worksheet that is different. I do not
know how to replicate to keep the cell reference the same but change the
worksheet name ref in the formula.

Try this statement for referencing all cells in place across multiple sheets:

=Sum(SheetName1:SheetNamex!$c$r)

where SheetName1 is the name of the left most sheet and SheetNamex is the
name of the rightmost sheet. All sheets must be adjacent in the series and
$c is the absolute reference of the cth column, and $r is the absolute
reference of the rth row, of the cell you want to sum or include in some
other funciton.
-Ralph
"Cloudburst99" wrote:

How do you replicate a formula on a summary sheet in Excel that takes its
data from individual cells in different worksheets? I know how to replicate
formulas when working with one worksheet but not when the formulas reference
multiple worksheets. However, the cell reference is the same on ALL
worksheets and it is the NAME of the worksheet that is different. I do not
know how to replicate to keep the cell reference the same but change the
worksheet name ref in the formula.

If you want to replicate that formula across rows then remove the "$" before
the row number in the first formula. Choose Enter after editing. Then
reselect that formula. Click-drag the lower right control handle down across
the rows. The row reference should then update relatively. That might help
you replicate the formulas by switching the row portion of a reference from
absolute ($) to relative reference (withou the $).

"Ralph Howarth" wrote:

Try this statement for referencing all cells in place across multiple sheets:

=Sum(SheetName1:SheetNamex!$c$r)

where SheetName1 is the name of the left most sheet and SheetNamex is the
name of the rightmost sheet. All sheets must be adjacent in the series and
$c is the absolute reference of the cth column, and $r is the absolute
reference of the rth row, of the cell you want to sum or include in some
other funciton.
-Ralph
"Cloudburst99" wrote:

How do you replicate a formula on a summary sheet in Excel that takes its
data from individual cells in different worksheets? I know how to replicate
formulas when working with one worksheet but not when the formulas reference
multiple worksheets. However, the cell reference is the same on ALL
worksheets and it is the NAME of the worksheet that is different. I do not
know how to replicate to keep the cell reference the same but change the
worksheet name ref in the formula.