In Numbers, I am using the Template: Monthly Budget

Question

Level 1

12 points

In Numbers, I am using the Template: Monthly Budget

I want to make a variation of the “Monthly Budget” provided in Apple’s Numbers App.

At present, the monthly budget shows just one month, a chart for that month with a budgeted amount on one sheet & the transactions on a second sheet. I would like to do a single year with 12 sheets which would have the budget for that month and the transactions for that month on just one sheet, if possible. Then each sheet would be for a single month.

My question today is about monthly subscription payments like Netflix, the NYT, Apple Music etc that come in on about the same day every month and for the same amount. I would like to make a formula that would automatically input the data on to the correct sheet for that next month with the correct date, the correct amount, with the name of the vendor. But I haven’t any idea how to do that being a complete novice with formulas etc.

All the other payment data would be completely random showing expenses as I input them. Just the subscriptions would be copied by the formula to the next month showing the correct date, preferably showing up on the date of payment, not before. How could I set that up for all the different subscriptions that one seems to collect these days??

iPad Pro, iPadOS 15

Posted on Sep 18, 2022 11:58 PM

Reply

Answer 1

Sep 22, 2022 12:00 PM in response to PTakeshige

Hello PTakeshige,

For help with formulas and changing an existing formula in Numbers, please check out the following link for information on how to do that: Calculate values using data in table cells in Numbers on Mac

Change an existing formula

You can edit an existing formula so that it refers to different cells.

1. Double-click the result cell with the formula you want to edit.

The formula editor opens, displaying the formula. Drag the left side of the formula editor to move it.

2. Do any of the following:

• Change the cell references: Select the existing cell addresses you want to change, then select the new cells.

• Remove cell references: In the formula editor, select the unwanted cell references, then press Delete on your keyboard.

• Add more cell references: Place the insertion point within the argument area for the formula, then click the new cells you want to add.

3. Press Return or click in the formula editor when you’re done.

If you click , you exit the formula editor without saving your changes.

If there’s an error in your formula, appears in the result cell. Click it to see the error message. If the message indicates another cell is causing the error, you can click the cell reference to select the cell with the error.

If you need additional assistance, please contact Apple Support. You might be able to reach them here: Contact Apple Support

Thanks!

Reply

Answer 2

Badunit

Level 7

28,024 points

Sep 22, 2022 5:56 PM in response to PTakeshige

This may be more than you can do at the moment but, to complete my thought (and correct a few things), here is a way to set it up.

First I created a sheet with a table for the repeating transactions.

Then I create a sheet for January

A few things I did:

Made the template into one sheet as per my previous post
Created a Repeating Transactions table (leave it blank for now blank)
Changed the sheet name to January
Deleted the "Transactions" text box and the text below it.
Turned on the table titles for the Transactions and Repeated Transactions tables and made the font bold and larger
Adjusted the table title font for the Summary table to match the font used for the other tables
Modified the formula in column C of the Summary table as per my previous post

The formula in A2 of the Repeating Transactions table is

=IF(Repeating Transactions::Repeating Transactions::A2≠"",DATEVALUE(TEXTBEFORE(REFERENCE.NAME($A$1,2),"::"))+Repeating Transactions::Repeating Transactions::A2−1,"")

Fill down with that to complete the column. The formula gets the month and year from the name of the sheet and gets the day from the table on the Repeating Transactions sheet.

The formula in B2 of the Repeating Transactions table is

=IF(Repeating Transactions::Repeating Transactions::B2≠"",Repeating Transactions::Repeating Transactions::B2,"")

Fill down to the end of B and across to D to complete the table

I recommend locking the Repeated Transactions table on this sheet. It is not something you want anyone to be able to edit.

You will want to clear the transactions table of data before continuing. Delete everything except column C because it has pop-up menus in it. For column C, set the first pop-up to blank then drag-fill that to the bottom of the table.

Duplicate the sheet. Change the name of the new sheet to February 2022. Repeat for the other months.

Reply

Answer 3

Badunit

Level 7

28,024 points

Sep 22, 2022 5:02 PM in response to PTakeshige

Your signature has iPad Pro as your device so I do not know if this is a iOS Numbers question or a Mac OS Numbers question. My answers will be for Numbers on Mac OS (this is the Numbers for Mac OS forum and I do not have an iPad).

There is a template named "Personal Budget" that sounds like the one you are talking about. To get everything from the Transactions sheet onto the Budget sheet:

Go to the Transactions sheet
With nothing selected, select all (Command A)
Cut (Command X)
Go to the Budget sheet
Paste (Command V). It will be a mess right now. Be careful not to deselect what you just pasted.
Drag downward on some part of what you just pasted to move it to the bottom of the sheet.
Delete the Transactions sheet

For your repeating transactions, it might be easiest to put them all in the first rows of the first month's transactions then copy/paste them into the same rows of the other months. Leave the dates blank or update the months for each (not very fun to have to do).

Alternatively, you could create a new "Repeating transactions" table. You would enter these transactions into the January "repeating transaction" table and the other month's "repeating transaction" tables would be a simple formula to get them. But then you have to modify the formulas in the Summary table to sum up those transactions also.

The formula in the other "repeating transactions" tables would be

cell A2 =IF(January::Repeating Transactions::A2<>"", January::Repeating Transactions::A2,"")

Copy/paste that from A2 to the other cells in the table

Reworked formula in column C of the Summary table would be

cell C2 =SUMIF(Transactions::C,A2,Transactions::$D)+SUMIF(Repeating Transactions::C,A2,Repeating Transactions::$D)

Copy/paste or drag fill to the other cells in column C

I did not test either of these formulas. I apologize in advance for any typos or syntax errors.

EDIT: I neglected to account for the change in the date from one month to another. The formula in column A (date) of the "repeating transactions" table will have to add one month to the dates. The formula given above will work for the rest of the columns (I hope) but use the one below for column A.

A2=IF(January::Repeating Transactions::A2<>"", EDATE(January::Repeating Transactions::A2,1),"")

fill down to the rest of column A

Reply

Answer 4

PTakeshige Author

Level 1

12 points

Sep 25, 2022 8:14 PM in response to Badunit

These are the file modifications I have made so far. I have a spreadsheet with 3 sheets so far, Repeating Transactions, January 2022, February 2022. The file itself is named “2022”.

The January Sheet looks like this:

#1

I have reformatted the file in Numbers as you suggested. The first sheet is a Repeating Transaction Table. I assume this is a Master Table…

#2

I have combined the Monthly Budget, Summary by Catagory, and another Repeating Transactions Table and the Transactions table into one sheet named January 2022.

#3

I have duplicated that January sheet, making a February sheet.

#4

Previously, following your directions, I had gotten the amounts in the Repeating Transactions Table to be counted (summed up) with the correct Category from Transactions in the Summary Table. But that was when I just had the one Repeating Transactions table next to the Transaction Table. But once I made a separate sheet for the Repeating Transactions and the separate January Sheet, it no longer worked. I suspect it has to do with my failure in #5 below.

#5

I have not succeeded with the following formulas. I get a Syntax error. And the Repeating Transactions Table on the first sheet is not added to the Transaction in the Summary Table.

Which table do I add these formulas to??

The one on the single sheet or the ones for the 12 months on the subsequent sheets???

The formula in A2 of the Repeating Transactions table is

=IF(Repeating Transactions::Repeating Transactions::A2≠"",DATEVALUE(TEXTBEFORE(REFERENCE.NAME($A$1,2),"::"))+Repeating Transactions::Repeating Transactions::A2−1,"")

Fill down with that to complete the column. The formula gets the month and year from the name of the sheet and gets the day from the table on the Repeating Transactions sheet.

The formula in B2 of the Repeating Transactions table is

=IF(Repeating Transactions::Repeating Transactions::B2≠"",Repeating Transactions::Repeating Transactions::B2,"")

Fill down to the end of B and across to D to complete the table

Reply

Answer 5

PTakeshige Author

Level 1

12 points

Sep 26, 2022 7:48 PM in response to Badunit

Okay, these are the formulas copied and pasted, not retyped from the Repeating Transactions Table on the January sheet.

#1

From the “Repeating Transactions” table on the January Sheet for Day of the Month

IF(Repeating Transactions::Repeating Transactions::A2≠"",DATEVALUE(TEXTBEFORE(REFERENCE.NAME($A$1,2,reference-style),"::",occurrence))+Repeating Transactions::Repeating Transactions::A2−1,"")

#2

From the Repeating Transliterations Table on the January sheet for Description

IF(Description 6≠"",Description 6,"")

This is strange as I did not type the above formula, I copy and pasted the formula that you had suggested but when I look at it again it turns into this other formula by itself.

#3

This is for the catagory

IF(Repeating Transactions::Repeating Transactions::B2≠"",Repeating Transactions::Repeating::B2,"")

#4

But here is the formula for C3 Catagory which seems different for some reason.

IF(Category 6≠"",Category 6,"")

#5

This is for the Amount

IF(Amount 6≠"" Amount 6,"")

Again, I did not type the formula like this. I used your formula but once I added it, it changed by itself.

I don't Know where all those “6s” come from. In the other formulas too.

#6

This is the formula for the C2 in the Summary Table:

SUMIF(Transactions::Category,$A2,Transactions::Amount)+SUMIF(Repeating Transactions::Category,A2,Repeating Transactions::$Amount)

______

I am sorry if I have misunderstood your suggestions and advise and done the wrong thing.

This will be very useful and I really do appreciate all your help.

Pamela

Reply

Answer 6

PTakeshige Author

Level 1

12 points

Sep 22, 2022 5:14 PM in response to Badunit

Badunit, thank you very much. I am using an iPad Pro, iOS 15. I have an iMac, but it is old and I don’t really use NUMBERS on it.

I will give it a try on my iPad. But I am totally a novice regarding formula writing in general, fascinating though it is.

Yes, perhaps a repeating transaction table would be a good start. I don’t mind if they all come at the beginning of each month in the transactions listings even if the datas are all over the place. But I would like to be able to input them one time when they start and to indicate that it’s a repeating monthly payment, and then have it appear in the next month’s budget automatically.

I know I need to study more.

Reply

Answer 7

PTakeshige Author

Level 1

12 points

Sep 25, 2022 5:04 PM in response to Badunit

I just wanted to let you that I am indeed working on this. I am limited in the amount of time I can apply at one time, but I am still working on it. With your formula in the above answer and the procedure you recommended above, so far, I have gotten the “Transactions” table and the “Reoccurring Transactions” table formula all on one sheet AND to show the SUM in the “Summary by Catagory” table. I just need to,get all those formulas into each of the appropriate cells.

I am getting an error message with the A (Date) and B (Description) column formulas in the Reoccurring Transactions table, but I will keep on working in them.

I have learned so much in just following your advice and direction. I really appreciate your help. I will keep you up-to-date with my progress. I have to admit, I do not exactly understand all that I am doing, but by keep on checking the formulas and testing them, I hope to get there!

Reply

Answer 8

Badunit

Level 7

28,024 points

Sep 25, 2022 10:40 PM in response to PTakeshige

I see you added a few columns to the Transactions table. Were you going to add those same columns to all of the Repeating Transactions tables? I think you should for consistency and looks and so the formula in the Summary by Category table is consistent (pulling data from the same columns in both tables). It is not necessary, though.

(EDIT: I took down the link I posted a few minutes ago. We'll work with what you have so far. And I find it very disconcerting that a link to a document in iCloud remains "shared" and accessible even AFTER I have deleted the actual document from iCloud. So you may still be able to access it).

I am assuming in the Repeating Transactions table in January that you entered a formula into A2 and B2 but did not yet copy/paste those formulas to other cells. What is the error in A2/ What is the error in B2? Copy/paste the formulas from those cells to here (not retype them here, copy/paste them to here) so we can see the problem.

Also post the formula from cell C2 of the Summary table.

Reply