#ref! errors after row deletion

[vanwooten]

I have sheet with a table column that uses the cell above to keep a running value:

=IF(ROW()-ROW(Trades[#Headers])=1, $N$6+S9,SUM([@[Trade Profit]]+X8)) - this is the first in the databody column (X9).

I also have a need to delete older rows at the top of the sheet. The top cell works after deletion but all the remaining rows have a #ref! error in place of the X cell at the end.
I have tried all kinds of things like:

WS.Range("X9").Copy
WS.Range("X10:" & (RowCount + 9)).PasteSpecial Paste:=xlPasteFormulas

Any ideas.

 

[Jamie McMillan]

Hello Vanwooten,

When you delete the row - you are deleting information needed by all the following rows.

A simple example to explain is:-

In cell A1 you type 1 - A2 is the formula = A1 +1 and copy it down to A10.

If you now delete row 7 - all that data is gone. (Row 8 becomes row 7, etc.)

But the reference =A7+1 refers to a deleted row, but it is no longer there - so Excel has no reference.

Jamie McMillan

 

[vanwooten]

Hello Vanwooten,

When you delete the row - you are deleting information needed by all the following rows.

A simple example to explain is:-

In cell A1 you type 1 - A2 is the formula = A1 +1 and copy it down to A10.

If you now delete row 7 - all that data is gone. (Row 8 becomes row 7, etc.)

But the reference =A7+1 refers to a deleted row, but it is no longer there - so Excel has no reference.

Jamie McMillan

I understand the problem. I am looking for a workaround. The top cell works because its value come from the other side of that IF. Once I have that first value though, I should be able to fill out the column.

 

[Jamie McMillan]

Hello Vanwooten,

If you copy the previous row down to the next cell - after you delete the row. In the example I sent. Delete row 7 then copy row 6 down.

Jamie McMillan

 

[Jamie McMillan]

Hello Vanwooten,

If you copy the previous row down to the next cell - after you delete the row. In the example I sent. Delete row 7 then copy row 6 down.

Jamie McMillan

Hello Vanwooten,

Or copy all the rows below and paste them onto row 7. (Then delete the last row - a duplicate)

Jamie McMillan

 

[vanwooten]

Hello Vanwooten,

If you copy the previous row down to the next cell - after you delete the row. In the example I sent. Delete row 7 then copy row 6 down.

Jamie McMillan

Yes it's easy to do manually but I have been asked to automate for the user the deletion of records before an entered date. This table column feeds a chart. I've got some other stuff to try yet.

 

[Peter_SSs]

What is the actual range of the table? I'm trying to determine if S9 is in the table or not. If it is, what is the table column heading in column S?
What worksheet column is the table column 'Trade Profit' in?

Taking a guess that 'Trade Profit' is in column S and you want a running total of cell N6 (outside the table) and the values in the 'Trade Profit' column the try this in the top cell after removing the existing formulas.

=N$6+SUM(INDEX([Trade Profit],1):[@[Trade Profit]])

If that is not it then perhaps you could answer the above questions or better still, provide a small sample of the table with XL2BB (with irrelevant columns hidden first) and explain further in relation to the sample.

 

[Peter_SSs]

Taking a guess that 'Trade Profit' is in column S and you want a running total of cell N6 (outside the table) and the values in the 'Trade Profit' column ..

Looks like it must have been a lucky?/educated? guess.