formula to follow preceeding value

M

Mike

Hi,

Is there a function in Excel that will read the cell directly above itself in a column and follow on as the next number. I am currently using the formula ="C4"+1 etc, but this is not an ideal formula as when I insert or delete rows things start to go wrong !.

Any help v much appreciated

Thanks,

Mike
 
K

Kjell

Jepp

It's something like this "=SUM(R[-1]C)"

R = rows C= column and in this case the [-1] means current ROW -1 (but the same column :)

"=SUM(R[-1]C[-1])" should thefore be one up and one left, and then you can add +1 or whatever....

Kjell
 
F

Frank Kabel

Hi Mike
maybe ROW() is the function you're looking for. Put
=ROW()
in call A1 and copy down

HTH
Frank
 
B

Bernie Deitrick

Mike,

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1

HTH,
Bernie
MS Excel MVP

Mike said:
Hi,

Is there a function in Excel that will read the cell directly above
Click to expand...
itself in a column and follow on as the next number. I am currently
using the formula ="C4"+1 etc, but this is not an ideal formula as
when I insert or delete rows things start to go wrong !.
 
B

Bernie Deitrick

Kjell,

That's the same formula that he has, just in RC notation. It has the
same problem that he is talking about.

HTH,
Bernie
MS Excel MVP

Kjell said:
Jepp

It's something like this "=SUM(R[-1]C)"

R = rows C= column and in this case the [-1] means current
Click to expand...
ROW -1 (but the same column :)
"=SUM(R[-1]C[-1])" should thefore be one up and one left, and then you can add +1 or whatever....

Kjell
Click to expand...
 
F

Frank Kabel

another way if you always want to add 1 to the preceeding row try the
following for column A
=INDIRECT("A" & ROW()-1)+1

Now you can delete rows in between. the formula will still work
Frank
 
Top