If() and then putting formual in the cell

  • Thread starter Thread starter Andreww
  • Start date Start date
A

Andreww

Hi - I have a formula in cell Q2 which is =I2, if the row is the 2nd row
(i.e. below title row). If it's the 3rd row it's =I3+Q2, 4th ==I4+Q3, etc
the point being that it accumulates after row 2 as row 2 is effectively the
first row... still with me.

The user now, quite reasonably, wants to sort the worksheet. So... the
formulas will all go out of synch and the accumulations will be incorrect!

What I want to do is put in a trap to check if the row is the 2nd/not - if
it is it gets I2, if it's n'th it gets In+Q(n-1) (i.e. if it's 7th it
becomes =I7+Q6).

I know that row() detects which row the cell is in, but I don't know how to
get the formula back into the cell - if(row()=2,"=I"+row(),.....)

Any ideas without a Macro???

TIA

Andrew
wwwdotjmdatadotcodotuk
 
Hi Andrew

If I understand the problem, formula in Q2:

=I2+IF(ROW()>2,Q1)
Fill down.
 
Hi
don't think this is possible (if I understood you correctly). How do
you want to store the 'ORIGINAL' row number. After sorting the row is
changed.
the only way I could come of would be the following:
enter in Q3
=INDIRECT("I4") + INDIRECT("Q3")
But you have to enter this fromulas manually for all rows (they won't
change while copying)
 
Hi

I'm not fully sure what do you want to do!

When you want, that after sorting the running sum for new order is
calculated, then no problems. Maybe you only have to use somewhat different
formula - for Q2
=SUM(I$2:I2)
and then copy it down. When the table is sorted, the formulas are
readjusting.

When you want, that after sorting old sums are preserved and moved to new
locations aling with rest of data on same row, then no way for it using
formulas. Convert formulas to values (PasteSpecial.Values) before sorting.
 
The answer I settled on is this:

In each cell (col P) I have where col "I" is what I am trying to accumulate.

=if ( row()=2 , indirect("I" & row() ) , indirect("I" & row() ) +
indirect("P" & row() -1 )) )

It was the indirect() function that was the important bit. I can now sort
the worksheet and the "sense" of the cell containts is retained.

Thanks for help group!

Andrew
wwwdotjmdatadotcodotuk
 
Back
Top