How do I send row data to different tables based on date

I'm assuming "the Budget template in Numbers" is the Personal Budget template, and that the current template is operationally the same as the one provided with my earlier version of Numbers.

The template has two Sheets, one named Transactions, the other named Budget.

Step 1: Drag the tab of the Transactions sheet left to place it in the leftmost position in the list of sheets at the top of the document window, with the Budget sheet's tab to its right.

Step 2: Select the tab for the Budget sheet, then click the small v toward the right side of that name and choose Duplicate. The new tab will contain the sheetname Budget-1. Click the tab to bring that sheet to the front, then make the edits below to the Table on that sheet:

Change the name of the table to show the first month of your budget year. (I chose November as that was the month of all the transaction dates in the document created by the template.)

Replace "Categories" in cell A1 with the date for the first day in the month named in the Tablename. SUMIFS can be a little picky about matching date values, so use the same format in this cell as you use in Transactions.

Enter the formula shown below the table in C2, then fill it down column C to the last row containing a category label in column A (row 10 in the example).

The Sum formulas in the Footer row and the Difference formulas in column C may be left as is.

The values in column B are monthly values, and are entered directly in the template. If these values are the same each month, enter your monthly budget amounts for each category into the appropriate cells of column B now.*

Step 3: Duplicate the edited table

Use the method described in step 2 to make 11 more copies of this sheet. Rename each sheet and its table with the name of the month for which it will be used, and change the date in A1 to the first day of that month.**

Check the formula in C2 to ensure that the cell ranges and cell references are the same as they were in the first edited version. Edit if needed.

Step 4: Edit the original summary table to create a YTD summary table.

This would sum the budget amounts and spending amounts in each category for the months up to and including the current month. Formulas would depend on whether the budget is distributed with a fixed amount allotted in each category to each month, or is varied month to month. Actual costs would be gathered from the totals cells for each category.

Notes:

*With an even monthly distribution of the budget amounts, these amounts could be retrieved from an auxiliary table listing the annual budget allotted to each category.

**These dates can be entered individually, or can be generated from the date entered in the first of the monthly summary tables. For months 2 to 12, the formula in A1 would be:

EDATE(MONTHNAME BY CATEGORY::$A$1,1) where MONTHNAME is the name of the preceding month as written in the name of that month's table.

Enough to chew on for now. Will check in tomorrow for further questions.

Regards,

Barry

Hi Steve,

Your formula as shown in the post above copied here) does not appear to have a syntax error.

Syntax for SUMIFS is:

SUMIFS(sum-values,test values,condition,test values,condition…) with as many test values,condition pairs as needed.

Mapping your formula against the syntax shown this:

SUMIFS(sum-values, test values, condition, test values, condition, SUMIFS(Checking::debit,Checking::Category,A2, Checking::Date,">="&$A$1,

test values, condition)

Checking::Date, "<"&EDATE($A$1,1)

Translated into English, the formula says:

Calculate the SUM of all values in the Debit column of the table named Checking on the rows where the Category column contains the value in 'this row' (2) of column A on 'this table', the date in the Date column is on or after (">=") the date in cell A1 (fixed) of 'this table', and the date in the date column is before ( "<" )the date in cell A1 (fixed) of 'this table.

The $ is the absolute reference operator, which holds the column or row reference (or both) to the same value as the formula is filled to the right or down a column.

The & is the concatenation operator which joins the value in cell A1 to the 'greater than or equal to' comparison operator (>=) or to the 'less than' comparison operator (<) to determine that the dates f the transactions being summed lie between the first date of 'this month and the day before the first day of the next month.

Adding the = to the 'less than' operator does not affect the syntax, but does change the operator to the 'less than or equal to' operator, adding the February 1 transactions to the set being summed for January.

Regards,

Barry

This expanded template (Dropbox download) I made a few years ago to account for multiple months may give you some ideas.

SG

Hi Steve,

You're welcome.

Happy to see you've arrived at a solution.

Regards,

Barry