reference cell above even if row deleted

[John Keith]

I would like to be able to reference a cell imediately above the
current cell even if the row above that cell had just been deleted.

For instance:

contents of A5: =A4+$B$1

then delete row 4 and rather than have contents of A4:#REF!+$B$1
have it A4:A3+$B$1

Is there some way to do this?

Thanks for any tips.


John Keith
(e-mail address removed)

 

[Debra Dalgleish]

You can use the OFFSET function: =OFFSET(A5,-1,0)+$B$1

 

[Dave Peterson]

How about:

=$A$1+OFFSET(A5,-1,0)

I would like to be able to reference a cell imediately above the
current cell even if the row above that cell had just been deleted.

For instance:

contents of A5: =A4+$B$1

then delete row 4 and rather than have contents of A4:#REF!+$B$1
have it A4:A3+$B$1

Is there some way to do this?

Thanks for any tips.

John Keith
(e-mail address removed)

 

[Soo Cheon Jheong]

John,

Enter this formula in A5:

=OFFSET(B5,-1,-1)+$B$1



--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--

 

[Dave Peterson]

oops. $a$1 should be $b$1.

(just to match the others!)

 

[John Keith]

Thanks everyone. It appears that OFFSET is a command I need to put in
my bag of tricks. I'll try it in the morning.


John Keith
(e-mail address removed)