9 Replies Latest reply: Nov 26, 2012 10:41 PM by Barry
JackieforMacs Level 1 Level 1 (0 points)

I'm trying to find a budget template for Numbers -- adapted from the checkbook template, maybe? --

 

where one can enter daily transactions (with category) in 12 different monthly tables, show each month's category sums in tables next to the daily ones,

 

and also have a table for Year-to-Date category sums, probably at the top of the whole thing.

 

In other words, each individual transaction would have to be added to its monthly category table, AND to the Year-to-Date category table.

 

Has anyone done this?  Can it be done in Numbers?

 

Thanks.


iMac, OS X Mountain Lion (10.8.2)
  • 1. Re: Need template with daily entries, & monthly sums & year-to-date sums
    Wayne Contello Level 6 Level 6 (13,620 points)

    You could start with the Existing Number template for a checkbook and duplicate the existing sheet for each month of the year (so there would be twelve).  Then add a 13th sheet to summarize the year.  There would be 12 rows and as many columns as categories.

     

    Just a suggestion.

  • 2. Re: Need template with daily entries, & monthly sums & year-to-date sums
    Barry Level 7 Level 7 (29,180 points)

    Hi Jackie,

     

    Why would you want to enter each transaction twice? That introduces a second chance to make an error on one of the entries and leave yourself in a situation where you could spend hours looking through th document trying to find where the error was made.

     

    Enter all of your transactions onto the main table (such as the Transactions table in the Check Register template, then set up the spreadsheet to pull off the transactions for each month onto a separate table without your having to do any retyping.

     

    That would be my suggestion.

     

    Regards,

    Barry

  • 3. Re: Need template with daily entries, & monthly sums & year-to-date sums
    JackieforMacs Level 1 Level 1 (0 points)

    Barry,

     

    I guess I didn't make myself clear.

     

    The user would enter the transaction amount once...

    and then the amount should automatically appear added to that month's Category Table, and

    to the Year-to-Date table in the same category, at the same time.

     

    I have not been able to figure out how to design my own template -- it seems beyond me when I get

    to the functions --

     

    so I was hoping someone had already figured out this problem,

    and created a template.

     

    Thanks to Barry and Wayne for their replies.

     

    Best, Jackie

  • 4. Re: Need template with daily entries, & monthly sums & year-to-date sums
    Barry Level 7 Level 7 (29,180 points)

    Hi Jackie,

     

    That makes more sense than my original reading. The function you are looking for is "SUMIFS."

     

    Here's an example, with the transactions recorded in the "Data" table, and the January sums reported in the "Summary" table.

    Picture 4.png

    "Data" contains no formulas.

     

    On "Summary":

     

    A1 contains the start date, Jan 1, 2012.

    The rest of column A contains the category names, which must exactly match the names used in the Data table.

     

    B2 contains the formula, which is filled down the rest of column B:

     

    Summary::B2: =SUMIFS(Data::$C,Data::$B,"="&A,Data::A,">="&$A$1,Data::$A,"<="&EOMONTH($A$1,0) )

     

    For an amount to be included in the SUM, three conditions must be met:

    -- The transaction category must match the category in that row of Summary.

    -- The transaction date must be on or after the date in A1 of Summary

    -- The transaction date must be on or before the last day of the month in A1 of Summary.

     

    Regards,

    Barry

  • 5. Re: Need template with daily entries, & monthly sums & year-to-date sums
    JackieforMacs Level 1 Level 1 (0 points)

    Thanks, Barry,  I think I get it now.

     

     

    Now I just need an enthusiastic unpaid intern! 

     

     

    We've also had a frustrating time finding out what templates are in Numbers' package of 100 templates.

    there does not seem to be a preview function on their site.

     

    Do you know how to view them before buying?

     

    Thanks again,

     

     

    Jackie

  • 6. Re: Need template with daily entries, & monthly sums & year-to-date sums
    Barry Level 7 Level 7 (29,180 points)

    "We've also had a frustrating time finding out what templates are in Numbers' package of 100 templates.

    there does not seem to be a preview function on their site.

     

    Do you know how to view them before buying?"

     

    Can't say i'm even aware of the package. What's the site?

     

    Regards,

    Barry

  • 7. Re: Need template with daily entries, & monthly sums & year-to-date sums
    JackieforMacs Level 1 Level 1 (0 points)

    Barry,

    I looked fir it again and I must have misread...

    I guess it was the original numbers Templates bundle...

     

    ANYWAY

    I finally figured out how to make that template...

    I will probably post it

     

    I'm assuming that I would post it on this site....

     

    Any other suggestions?

     

    Thanks, Jackie

  • 8. Re: Need template with daily entries, & monthly sums & year-to-date sums
    JackieforMacs Level 1 Level 1 (0 points)

    Wayne,

     

    I did wind up using the template basic design you suggested...

     

    Thank you---

     

    I finally figured out the formulas!

     

    See also the reply above...

     

     

    Best, Jackie

  • 9. Re: Need template with daily entries, & monthly sums & year-to-date sums
    Barry Level 7 Level 7 (29,180 points)

    Hi Jackie,

     

    I don't think there is a way to post a file (including a template file) on Apple Support Communities ('this site'). I'd like to see a screen shot and description, though.

     

    You might try the iWork Community site as a place to post the actual template.

     

    Regards,

    Barry