Excel Format Question Please Help!!!!

[Cicyhua]

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>

 

[Arithos]

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

 

[WarPigl3t]

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.

 

[Cicyhua]

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!!

 

[Cicyhua]

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!!

 

[Arithos]

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