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.
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.
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.
"Data" contains no formulas.
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.
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?
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.