Referencing Problem

[joeyjj]

Hello All,

I would like to use the following code:

=IF(A7 = "",1, SUM(1+A7:A7))

Note that A7 refers to the cell above the current cell

however, if I delete the row above (Row 7) I get a #REF! error. My code looks like this:

ws.Cells(iRow, 1).Formula = "=if(A" & iRow - 1 & " = """",1, SUM(1+A" & iRow - 1 & ":A" & iRow - 1 & "))"

and I would like it that if the row above is deleted that the if statement is always selecting the row above.

 

[Saagar]

Before solving this for you, I have a question:

Is there a reason you are using sum(1+A7:A7) instead of simply using 1+A7?

 

[joeyjj]

That would be a mistake lol. It should only be 1+A7

 

[Saagar]

Lets say, you want to put a formula in A8 referencing the cell above it and add 1 to it, you can use this function:
=SUM(OFFSET(A8,-1,0,1,1))+1

If you delete a row above, excel will auto-adjust the function to make it reference the cell above it.

Hope this helps.

 

[joeyjj]

Awesome thanks soooo much!!!