Excel Format Question Please Help!!!!

Cicyhua

New Member
Joined
Aug 27, 2014
Messages
6
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?


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Invoice Number[/TD]
[TD]Shipping Number[/TD]
[TD]Price[/TD]
[TD]Total[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12345[/TD]
[TD]98765[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]23456[/TD]
[TD]87654[/TD]
[TD]20[/TD]
[TD]15[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]34567[/TD]
[TD]65432[/TD]
[TD]30[/TD]
[TD]25[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[TD]60[/TD]
[TD]45[/TD]
[TD]P[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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?


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Invoice Number[/TD]
[TD]Shipping Number[/TD]
[TD]Price[/TD]
[TD]Total[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12345[/TD]
[TD]98765[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]23456[/TD]
[TD]87654[/TD]
[TD]20[/TD]
[TD]15[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]34567[/TD]
[TD]65432[/TD]
[TD]30[/TD]
[TD]25[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[TD]60[/TD]
[TD]45[/TD]
[TD]P[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Upvote 0
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.
 
Upvote 0
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!!
 
Upvote 0
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!!
 
Upvote 0

Forum statistics

Threads
1,223,492
Messages
6,172,599
Members
452,466
Latest member
Lynlindsay

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top