Background:

I have two worksheets, the first one includes massive download data contains different codes:P,A,C,ect. The second worksheet shows only the data with P code. The below table is what my worksheet2 looks like. The formula through A2-D4 is array formula. Cell C5-D5 contains sum formula. Cell A-B is a merged cell for text 'Grand Total". The second worksheet automatically updates any changes made in the first worksheet related to data with P . I replace the current data in worksheet1 with a new download every week.

Question:

The automatic update process stops in worksheet 2 if they reach the grand total row. For example, I have 10 new data this time but my worksheet only has 3 row available(as seen in the table, only row2,3,4 are available)

How do I solve this problem without deleting the grand total row ever ytime or leaving blank rows in my worksheet?

A | B | C | D | E | |

1 | Invoice Number | Shipping Number | Price | Total | P |

2 | 12345 | 98765 | 10 | 5 | P |

3 | 23456 | 87654 | 20 | 15 | P |

4 | 34567 | 65432 | 30 | 25 | P |

5 | Grand Total | 60 | 45 | P |

<tbody>

</tbody>