How to do 'Nested' cell references

[Guest]

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.

 

[Pete_UK]

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

 

[ilia]

Possibly offset is what you're looking for.

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