Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Creating a daily cash report showing # of trx and total amount

    I am trying to create a daily summary report from the daily report with the pivot table as an intermediate step.
    I have 6 locations and different people taking in the funds. I am attaching a copy of the workbook.

    How do I get from the DAILY REPORT to the DAILY SUMMARY. I thought I could use a PIVOT TABLE to total and then cross over to the DAILY SUMMARY but I am having trouble with the transition of the totals on a daily basis in the DAILY SUMMARY report which is the goal.

    Any thoughts out there?

    Thank you
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MNN,

    You don't really need the Pivot Table you can go directly to the Daily Summary with SUMIFS.
    The attached worksheet shows how to do this.
    I set up the formulas carefully so that you could make maximum use of copy and fill. I created formulas for D3:D8 they are identical except for the "Sum Range" These formulas can be highlighted as a group and filled to the right for as many date columns as you have in this cast to column F.

    You can NOT however fill DOWN! You can however copy D1:D8 and paste as a group into the next set starting in D11 and repeat for D19, D26, etc. Once copyed they can then be filled to the right.
    MNN Daily Summary.JPG
    You'll notice the use of 2 Dynamic Range Names MyLocations and MyDates these are setup to allow up to 60,000 rows in the DailyReport w/o having to make any adjustments just add entries as needed.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts

    Complex formulas make my head spin!

    Wondering if VB might be an alternative. The report can be built on the fly meaning that it is dynamic with dates and other criteria. This sample parallels RG's concepts but does calculations and data transfer through code. The Duplicate sheet is called "Summary" and is built on the Activate event subroutine of the sheet. So everytime you open or flip to the worksheet it will rebuild on the fly and update data. Before viewing the sheet, go in to design mode then view the sheet; you will see no dates or formulas except for the locked cells (no password). It will also accept max number of allowable rows without adjustments. I have only demonstarted the Cashier and Farm segments.

    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2013-01-04 at 06:28.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MNN,

    Here's an improved version of my original. I had the Sum range hard coded, verses using a Dynamic Range Name, because I wasn't thinking, it was late at least for us old codgers.

    The Dynamic Range Names I was using wouldn't work on the sum ranges since they contained blanks. Upon reflection this morning I realized that I just had to have the CountA portion of the formula look at the Date column to get the count of rows, doesn't matter which column you use for the count as long as it doesn't contain any blanks!
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I would rather go with the formula approach that VB. However, I really have not used the "OFFSET, COUNTA" at all. Could you give me a primer on this complex formula (s).

    Thanks.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MNN,

    I'll give it a go and hopefully you'll understand, if not post back and I'll try again.

    Here's one of the Dynamic Range Formulas the one for Credit Card Count { Number of CC transactions } named CCCnt.
    =OFFSET('DAILY REPORT'!$I$8,0,0,COUNTA('DAILY REPORT'!$B$8:$B$60008),1)

    Let's look at the COUNTA portion first: COUNTA('DAILY REPORT'!$B$8:$B$60008),1)
    The COUNTA function counts all the cells in a range that contain an entry, it will not count blank cells!
    COUNTA Arguments:
    Range in which to count entries. In this case 'DAILY REPORT'!$B$8:$B$60008
    Note as mentioned in the previous posts you need to pick a range to count that does not have any blanks in it if you want to include all rows in the data table. In the case of your example you can choose one of Columns A through D. I chose B since I concluded there would always be a date! Again since we want the total number of rows and obviously there are BLANKS in the "TOTAL # OF RECEIPTS - CREDIT CARDS" column we need to choose another column.

    Now we have the total number of rows we need to process.

    Now the OFFSET function will compute a range of cells given a base starting address.
    OFFSET Arguments:
    Base starting address {in this case I8 for Credit Card Count }
    Row Offset from starting address for start of range { 0 in this case since the base address doesn't need adjustment}
    Column Offset from starting address for start of range { 0 in this case since the base address doesn't need adjustment}
    Number of rows to include in the range {calculated with the COUNTA function in this case}.
    Number of columns to include in the range { a single column in this case }.

    With this as you add entries to the bottom of the list the COUNTA part of the Dynamic Range Name will recalculate the number of rows to be included. Just don't leave any blank rows!

    HTH. :cheers
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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