How to do 'Nested' cell references
 

I have a cell (C3) in a 'Totals' worksheet summing the contents of cells in
other worksheets (named APPL, ACRF, OMAC, OPNG & ACRD) by using the formula:
=sum(APPL!C72+ACRF!C72....) Simple, so far. I want to be able to edit the
row number - 72 - globally by refencing it in another cell (E23) on the
Totals worksheet such as: =sum(APPL!C[I23]+ACRF!C[I23]...) You can see what
I'm after... I want to enter '73' in cell E23 on the Totals worksheet and
have the formula sum the contents of the next row down from each of the
worksheets. Thanks.

How to do 'Nested' cell references
 

Have a look at the INDIRECT function in Excel Help - this allows you
to build up cell and range references as strings. For example, with 10
in E1, this formula:

=INDIRECT("Sheet2!A"&E1)

will return the value from Sheet2!A10.

Hope this helps.

Pete

On Oct 26, 8:04 pm, Larrydish
wrote:
I have a cell (C3) in a 'Totals' worksheet summing the contents of cells in
other worksheets (named APPL, ACRF, OMAC, OPNG & ACRD) by using the formula:
=sum(APPL!C72+ACRF!C72....) Simple, so far. I want to be able to edit the
row number - 72 - globally by refencing it in another cell (E23) on the
Totals worksheet such as: =sum(APPL!C[I23]+ACRF!C[I23]...) You can see what
I'm after... I want to enter '73' in cell E23 on the Totals worksheet and
have the formula sum the contents of the next row down from each of the
worksheets. Thanks.

How to do 'Nested' cell references
 

Possibly offset is what you're looking for.

=OFFSET(APPL!$C$1,E23-1,0)+OFFSET(ACRF!$C$1,E23-1,0)+....



On Oct 26, 3:04 pm, Larrydish
wrote:
I have a cell (C3) in a 'Totals' worksheet summing the contents of cells in
other worksheets (named APPL, ACRF, OMAC, OPNG & ACRD) by using the formula:
=sum(APPL!C72+ACRF!C72....) Simple, so far. I want to be able to edit the
row number - 72 - globally by refencing it in another cell (E23) on the
Totals worksheet such as: =sum(APPL!C[I23]+ACRF!C[I23]...) You can see what
I'm after... I want to enter '73' in cell E23 on the Totals worksheet and
have the formula sum the contents of the next row down from each of the
worksheets. Thanks.