I have the follwoing table:
Item qty Price Total Price
1 2 45 90
2 4 6 24
3 7 78 546
4 8 34 272
5 3 23 69
total
In column D I have the following formula
=IF(AND(X="",Y=""),"",IF(AND(X="",NOT(Y="")),"tota l",W*Z))
X = 3 columns to the left
Y = 3 columns to the left and one row up
W = 2 columns to the left
Z = 1 columns to the left

So in the cell that contains 24 (D3) I have the following formula
=IF(AND(A3="",A2=""),"",IF(AND(A3="",NOT(A2=""))," total",B3*C3))

If I delete Row 2 I get a reference error in the new D2. Is there any way
to change the formula so that if I delete a row that a cell below it
references to have the formula refence the new row above it?
So after deleting row 2 I want the cell that should now have 24 in it New D2
(old D3) to be:
=IF(AND(A2="",A1=""),"",IF(AND(A2="",NOT(A1=""))," total",B2*C2))

Thank you for any help you can provide.

=IF(AND(A3="",ADDRESS(ROW()-1,1)=""),"",IF(AND(A3="",NOT(ADDRESS(ROW()-1,1)="")),"total",B3*C3))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"OX_Gambit" wrote:

I have the follwoing table:
Item qty Price Total Price
1 2 45 90
2 4 6 24
3 7 78 546
4 8 34 272
5 3 23 69
total
In column D I have the following formula
=IF(AND(X="",Y=""),"",IF(AND(X="",NOT(Y="")),"tota l",W*Z))
X = 3 columns to the left
Y = 3 columns to the left and one row up
W = 2 columns to the left
Z = 1 columns to the left

So in the cell that contains 24 (D3) I have the following formula
=IF(AND(A3="",A2=""),"",IF(AND(A3="",NOT(A2=""))," total",B3*C3))

If I delete Row 2 I get a reference error in the new D2. Is there any way
to change the formula so that if I delete a row that a cell below it
references to have the formula refence the new row above it?
So after deleting row 2 I want the cell that should now have 24 in it New D2
(old D3) to be:
=IF(AND(A2="",A1=""),"",IF(AND(A2="",NOT(A1=""))," total",B2*C2))

Thank you for any help you can provide.

Luke,

Thank you for the help. It fixed my initial question, but when I drag the
formula down past the bottom the original "total" the cells are filled with
the word "total". I would only like the top most cell with nothing in column
A to be filled with the word "total"

It seems that ADDRESS(ROW()-1,1)=0 is not true in your formula therefore not
putting "" in the cell.

Thank you for any help that you can provide.
"Luke M" wrote:

=IF(AND(A3="",ADDRESS(ROW()-1,1)=""),"",IF(AND(A3="",NOT(ADDRESS(ROW()-1,1)="")),"total",B3*C3))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"OX_Gambit" wrote:

I have the follwoing table:
Item qty Price Total Price
1 2 45 90
2 4 6 24
3 7 78 546
4 8 34 272
5 3 23 69
total
In column D I have the following formula
=IF(AND(X="",Y=""),"",IF(AND(X="",NOT(Y="")),"tota l",W*Z))
X = 3 columns to the left
Y = 3 columns to the left and one row up
W = 2 columns to the left
Z = 1 columns to the left

So in the cell that contains 24 (D3) I have the following formula
=IF(AND(A3="",A2=""),"",IF(AND(A3="",NOT(A2=""))," total",B3*C3))

If I delete Row 2 I get a reference error in the new D2. Is there any way
to change the formula so that if I delete a row that a cell below it
references to have the formula refence the new row above it?
So after deleting row 2 I want the cell that should now have 24 in it New D2
(old D3) to be:
=IF(AND(A2="",A1=""),"",IF(AND(A2="",NOT(A1=""))," total",B2*C2))

Thank you for any help you can provide.

OX_Gambit wrote:
I have the follwoing table:
Item qty Price Total Price
1 2 45 90
2 4 6 24
3 7 78 546
4 8 34 272
5 3 23 69
total
In column D I have the following formula
=IF(AND(X="",Y=""),"",IF(AND(X="",NOT(Y="")),"tota l",W*Z))
X = 3 columns to the left
Y = 3 columns to the left and one row up
W = 2 columns to the left
Z = 1 columns to the left

So in the cell that contains 24 (D3) I have the following formula
=IF(AND(A3="",A2=""),"",IF(AND(A3="",NOT(A2=""))," total",B3*C3))

If I delete Row 2 I get a reference error in the new D2. Is there any way
to change the formula so that if I delete a row that a cell below it
references to have the formula refence the new row above it?
So after deleting row 2 I want the cell that should now have 24 in it New D2
(old D3) to be:
=IF(AND(A2="",A1=""),"",IF(AND(A2="",NOT(A1=""))," total",B2*C2))

Thank you for any help you can provide.

Why not just hide row 2?

Bill

That is not the complete sheet. In the cell next to "total" I will have the
sum of column D, so hiding the row will include that row in the sum and that
will give an incorrect total.

"Bill Sharpe" wrote:

OX_Gambit wrote:
I have the follwoing table:
Item qty Price Total Price
1 2 45 90
2 4 6 24
3 7 78 546
4 8 34 272
5 3 23 69
total
In column D I have the following formula
=IF(AND(X="",Y=""),"",IF(AND(X="",NOT(Y="")),"tota l",W*Z))
X = 3 columns to the left
Y = 3 columns to the left and one row up
W = 2 columns to the left
Z = 1 columns to the left

So in the cell that contains 24 (D3) I have the following formula
=IF(AND(A3="",A2=""),"",IF(AND(A3="",NOT(A2=""))," total",B3*C3))

If I delete Row 2 I get a reference error in the new D2. Is there any way
to change the formula so that if I delete a row that a cell below it
references to have the formula refence the new row above it?
So after deleting row 2 I want the cell that should now have 24 in it New D2
(old D3) to be:
=IF(AND(A2="",A1=""),"",IF(AND(A2="",NOT(A1=""))," total",B2*C2))

Thank you for any help you can provide.

Why not just hide row 2?

Bill