MS Excel question - refer to the previous cell not by name

Factual Questions
1

Is it possible to refer to a cell above or bellow the one containing a sum without refering to it’s name?

Say I am in cell b4 and I want to do a sum using what’s in b3 how can I refer to it without specifying ‘b3’.

The reason I ask is because if I delete an entire row (b3) then excel gives the #ref! error.

I want it to keep the reference and use the new contents of b3 after the row delete

2

You could make it an absolute reference rather than a relative on using dollar signs, The form for that is something like $B$2+$B$3

3

=OFFSET(x,y) will give you the contents of the cells x rows and y columns away (negative numbers go left / up, positive numbers go right / down.

4

Something else that might work for you–I don’t fully understand what you need–is INDIRECT.

=INDIRECT(“B3”)

will always give you a reference to the current B3, no matter what you do with additions and deletions. The argument to INDIRECT is text specifying the address of the cell you want. For instance, if you have

B2 1
B3 2
B4 5
B5 7

then =SUM(INDIRECT(“B2”):INDIRECT(“B5”)) gets you 15

5

I’ll try the offset.

While waiting for answers I was trying to use the r1c1 type of referencing (so that I could do R[0]C[-2] but it wouldn’t let me!

6

Offset worked , thanks iwakura43
=SUM(OFFSET(D17,-1,0)+(B17-C17))
It’s a simple financial sheet where I copied a month’s data to the following month, and then remove items that don’t apply.