Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2011
    Posts
    284
    Thanks
    33
    Thanked 2 Times in 2 Posts

    Totals in footer / header

    Is anyone aware of a method for including a total at the bottom of the page (for example in the footer) that can be carried over the the header of the next page?

    I normally have to include the page total and brought forward totals as lines within the spreadsheet. This works fine until you need to insert a row for an additional item.

    I would really like to know if this is possible. I have tried putting formulae into the footer, but that is simply printed out as the formula.

    (Office 2010 is being used at present.)

    Thanks in advance!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am not sure what you want exactly. I think what you want would require a macro to be run before printing to determine the total on each page and print a different header and footer for each page.

    If that is indeed the desire, we would need more information on the setup. Could you post an example file with what you want?

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2011
    Posts
    284
    Thanks
    33
    Thanked 2 Times in 2 Posts

    Sample File

    Here is a sample file - you will see that the amounts are calculated from the quantity multiplied by the rate for each specific item. At the bottom of the page, there is a Carried Forward item, where the page totals are added up, and then on the next page a Brought Forward item which will take to total through to the next Carried Forward item at the bottom of the page.

    This is a very common scenario, and as you can see, if you set it up to fill a page, with the Carried Forward items at the bottom, then inserting any new items will result in the pagination going for a loop.

    What I would like to do is insert the formula for the page totals into a footer, and the brought forward totals into the header..... Is that possible?
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    There is no simple way to do this since XL does not allow repeating rows at the bottom. A header and footer option is fine and can be done, but how do you want it formatted and aligned? Could you create a sample with the header and footer as you want them displayed (with the calculated numbers)? The header/footer route will look much different than the rows copied into the workbook as you have them, so I have no idea how you want them to look.

    This will allow to see how you want the header and footer to look in each section and then work on the means to put the calculations in it.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    Possibly using the updated file I've attached that includes several named ranges that will help as a starting point:

    1. The named ranges define:
    a. Relative ranges to allow the SUM() functions to be copied to any location and still return the correct result. See the sum functions on page 1 and 2 as examples.
    b. PrintBoundary range with the page summary and carry forward formula and column headers, which are presently at row 500.
    c. Upper left and lower right corners for the print area to allow the print to dynamically change but not include the PageBoundary range.
    d. Dynamic print area based on the upper left and lower right named ranges.

    2. Left as an excerise is to develop the macro(s) to rebuild the printout correctly formatted:
    a. Walk the print area from the bottom up - use the row from PrintLR as the starting point to locate and delete the current page boundary cells by locating the cells containing "CARRIED FORWARD" and deleting all the cells from the same row to the blank row below the "BROUGHT FORWARD" row. This produces a clean report that can be repaginated.
    b. Assuming 72 rows per page, walk the report from the top down locating the last heading in column A and it's sub headings in column B that fits within 69 rows. Copy the PageBoundary range below the blank row below this heading to fill in rows 71 and 72 on this page and rows 1 - 4 on the next page. Continue this until you reach the PrintLR row, and at that point all the pages will have 72 rows, each page ending with a "CARRIED FORWARD" row and starting with your header rows including the "BROUGHT FORWARD" row.

    This can be down with some loops in VBA and by recording some macros while performing the delete and copy/paste for part of the code.

    Hope this little bit of advise helps!
    Attached Files Attached Files
    PJ in FL

  6. #6
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    Sorry for the confusion, but this file contains the correct SUM() functions. I'd updated the file but didn't upload the correct version.
    Attached Files Attached Files
    PJ in FL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •