#### Cicyhua

##### New Member
Hi I have a question about excel formatting and the table is very close to what my worksheet looks right now. I have been struggling on this for days and I would be really appreciate if someone can help me out. Thanks!
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>

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi I have a question about excel formatting and the table is very close to what my worksheet looks right now. I have been struggling on this for days and I would be really appreciate if someone can help me out. Thanks!
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>

In my oppineon Merged cells ****s everything up, the reference used by your array formula may be A5, and that is not a precise reference anymore =P Just select all cells and unmerge, maybe that could help you

First Delete the Grand Total row. I'm assuming your current code will display the output dynamically. So if you have 10 rows with "P" then 10 will show up on sheet 2. If you have 3 rows with "P" then 3 will show up on sheet 2. If i'm right so far then the rest is easy. Now I don't know if you used an array formula or used an array macro. So if you used a macro, the following code will be placed at the end of your macro. If you used a formula, then you just need to make a macro now.
First you need to find out what the last row is in your sheet 2.
lastRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xldown).Row
So lastRow will be equal to 4 using your data from the table above.
Now you need to go to the next blank row so lastRow = lastRow + 1
Then enter your row Data, so
Sheets("Sheet2").Range("A" & lastRow).value = "Grand Total"
Sheets("Sheet2").Range("C" & lastRow).value = Application.Worksheetfunction.Sum(Sheets("Sheet2").Range("C2:C" & (lastRow -1)))
I made have made a syntax error in that last formula. I'm doing this from memory. It's pretty obvious what I'm doing there though so you shouldn't have a problem figuring it out.

Thank you so much for your help!! You are right the merged cell messes everything up lol. I'm going to give it a try. Thanks again!!

Thank you for your help! I used array formula and my current code will display the content corresponding to each cell coded with P because I used index formula to look up staff. For example, the second worksheet will show up the invoice number, price and the shipping number corresponding to the the first P founded in worksheet1.
I'm definitely gonna try to create a macro and see how things goes!!
Thanks so much!!

Thank you so much for your help!! You are right the merged cell messes everything up lol. I'm going to give it a try. Thanks again!!

Indeed, somehow it always does =P Glad to help

Replies
0
Views
327
Replies
3
Views
554
Replies
3
Views
252
Replies
4
Views
151
Replies
3
Views
636

1,196,487
Messages
6,015,498
Members
441,898
Latest member
kofafa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back