how to define "previous" cells?

  • Thread starter Thread starter A. H. K.
  • Start date Start date
A

A. H. K.

I need to get the sum of two previous cells.
But the row location name may change.

C3 = C1 + C2

but after sorting and re-group and move this may becomes row
E3 = E1 + E2


How can I make a general formula of the sum of two previous cells?
Thank you.
 
I need to get the sum of two previous cells.
But the row location name may change.
C3 = C1 + C2

but after sorting and re-group and move this may becomes row
E3 = E1 + E2

How can I make a general formula of the sum of two previous cells?
Click to expand...

One way:

=SUM(OFFSET(C3,-2,0,2,1))
 
Find two cells that don't move, say A1 and A2
Enter C1 and C2 respectively in them.
Use the formula... =INDIRECT(A1)+INDIRECT(A2)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"A. H. K."
wrote in message
I need to get the sum of two previous cells.
But the row location name may change.

C3 = C1 + C2

but after sorting and re-group and move this may becomes row
E3 = E1 + E2


How can I make a general formula of the sum of two previous cells?
Thank you.
 
I need to get the sum of two previous cells.
But the row location name may change.
C3 = C1 + C2

but after sorting and re-group and move this may becomes row
E3 = E1 + E2

How can I make a general formula of the sum of two previous cells?
Click to expand...

One way:

=SUM(OFFSET(C3,-2,0,2,1))

Can I use a formula that is not depending on location? This sum depends on
C3?
 
One way:
=SUM(OFFSET(C3,-2,0,2,1))

Can I use a formula that is not depending on location?
This sum depends on C3?
Click to expand...

I anticipated your question and tested the following:

=sum(offset($A$1,row()-1-2,column()-1,2,1))

It seems a litle arcane to me. Perhaps someone can suggest something
better. (Of course, "-1-2" can be reduced to "-3". I left it as is
to demonstrate the derivation.)

But Sort does change C3 as needed when it moves C3. So I thought it
would be okay. On the other hand, the same can be said for your
original formulation. So it is no longer clear to me why you need to
solve this "problem".

(Having said as much, I do know that I, too, have encountered problems
with sorting cells with formulas. But I think it was the reverse:
when Sort moves cells, it fails to update referring to the cells that
Sort moves.)
 
Back
Top