Solved

Excel 2010 need to add subtotals to the end of each page

Posted on 2014-07-21
9
1,235 Views
Last Modified: 2014-08-26
I am using Excel 2010. I have a worksheet that will grow over time.  It currently has 3 pages.  I need a subtotal to appear at bottom of each page, preferably not in the footer, but at the bottom of the text on each page.  Columns that need totals are L, M & O.  It could be that we will adjust row height and the number of rows per page may change at some point in time.
I have attached the worksheet.

Thanks for your help.
Bad-Debt-Log-Master-X-.xls
0
Comment
Question by:klitton7
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 14

Expert Comment

by:Don Thomson
ID: 40210055
I'm sure that there is some fancy way to accomplish that with a macro - However - The problem becomes when you add a new item (row)  it will wipe out the total.  If you know about how large the sheet will grow to, Put the total at the top of the column.   So the total(s) cell will be =sum(L7:L10000)    =sum(M7:M10000)   and =sum(N7:N10000)
It will ignore the blanks when adding everything up

If you MUST have them at the bottom of the page - you would have to insert a row  somewhere in the range that is being totaled
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40210116
Try this put the sum(L6:L60000) on the around  the top of the page so
that you can add many number rows under row6 without affecting  the sum value
Please see attached
Duncan
Bad-Debt-Log-Master-X-2.xls
0
 

Author Comment

by:klitton7
ID: 40210170
Thanks for your response Duncan, but I need each page to have a total for that page, and then a grand total on the last page.  Sorry, I'm sure I didn't make that clear.
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40210189
I just see one page in your workbook



Duncan
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 13

Expert Comment

by:duncanb7
ID: 40210209
Just reminder, did you know you can record what you edit or action you did or you want on Excel cell across different workbook and sheet into macro by Excel function of "Macro Recording" that will save  your expectation vba code into macro on VBA editor. Take a look at this Macro Recording tutorial at this site
https://www.youtube.com/watch?v=nvWpFdo7EO0

Duncan
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40210902
I looked at modifying the existing macros in your workbook, but none of them appeared to run properly, so I just created two new ones.  I added buttons to the sheet that will let you add or remove the subtotal lines at your discretion.

These macros will insert a subtotal line to the bottom of each sheet (every 26 rows of data) and add a final grand total line at the end.

A possible issue would occur if your final page had exactly 26 rows, in which case the grand total line would fall on its own page.  Otherwise, it should run with as many rows as you have in your worksheet.  With each iteration, you'd want to remove existing subtotals first, then add them back again.

Regards,
-Glenn
EE-Bad-Debt-Log-Master-X.xls
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40247599
Hi,

If you have any questions or issues with any of the solutions above, please let us know.

Otherwise, can you please properly close this question by clicking the "Accept this solution" link above the appropriate submission above that answers your question?.  This will help ensure that future searches are meaningful to other EE members.

If you still have issues or questions, please let us know.

Thanks,
-Glenn
0
 

Author Closing Comment

by:klitton7
ID: 40286175
Works great!  Thanks Glenn
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40286180
You're welcome.   Thank you for following up on this.

Regards,
-Glenn
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now