🏆 Budget Plan - Google Sheets

Google Sheets Show & Tell
,
61

I’m having an issue with using the Bi-Monthly Frequency. It doesn’t populate the monthly columns. When I switch to weekly, annual, etc I can see that the occurences column changes according to the selection but when I go to bi-monthly the occurences shows 0.

63

Sorry about that @pchristopherclark , I just looked, and somehow the formula for that part of the update wasn’t in the shared template. I just added it and bumped the version number to 1.71. It should be ready to download as soon as @randy gets a chance to bump the version in the add-on. Once updated, don’t forget to add the dates in the Notes column for Bi-Monthly as described in the documentation.

64

Hi team, having a few issues with the past-category function. I have tried a few categories with data I know is there, so I was wondering if anyone had any troubleshooting solutions. I have tried re-downloading the sheet but no dice. Wondering if it’s a formula issue or if it could be because the data used might not stretch a whole year? Any suggestions would be appreciated

65

The ‘Past’ frequency options look for data from 12 months before the month in question. To get a budget based on ‘Past-Category’ for November you need transactions in that category from November of last year.

66

Sorry for the delay. @jpfieber had this fixed fast and I was the bottleneck. 1.71 is live now, @pchristopherclark.

67

Why is the formula not working in sheet but work in excel? In sheet, no figure is showing in the categories sheet that feed other budgeting sheets. should date be in text or what format should date be to recognise the (F$1) ‘’=IF(ISBLANK($A2),“”,IFERROR(SUMIF(‘Budget Plan’!$E$4:$E$200,$A2,OFFSET(‘Budget Plan’!$L$4:$L$200,0,MATCH(DATEVALUE(F$1),‘Budget Plan’!$M$3:$X$3,0))),0))‘’

68

Starting with version 1.70 the Budget Plan sheet gets the date headings from the Categories sheet to ensure they match. The dates there use the m/d/yyyy format. In the Budget Plan sheet, it shows them as ‘mmm’. Do you get an error in the Categories sheet where you have that formula pasted, or is it just blank?

69

There is no error. Just blank

70

Some things to check:

  • Make sure your Budget Plan sheet is called ‘Budget Plan’. If it’s called anything else the formula won’t find it.
  • Make sure on the Budget Plan sheet that the first month of the budget period (likely Jan) is in column M
  • Make sure on the Categories sheet that the first month of the budget period (likely Jan) is in column F. If it isn’t, you need to change the “F$1” in the formula to reflect which column contains the first month of the period

Fingers crossed that it’s one of these so you can get it fixed!

71
  • The Budget plan is correct
    -First month is July because our financial year is from July-June
    -The categories and Budget period are the same. July. First month starts at G in categories hence “G$1”.

It was working ok until last two updates

72

I’m not sure how else to troubleshoot it at this point without seeing it. If you’re willing to share your sheet with me, send a link in a direct message and I’ll see if I can figure out where things are going wrong.

74

Ahh understood. So if the data I’m looking to work with starts in August, I should set my planning period as August next year?

75

I took a look at the template you shared and found that for some reason when the Budget Plan sheet pulls the dates of the budget period from the Categories sheet, nothing shows up, but if I manually type in the start date in M3 of the Budget Plan template, then things work. I changed yours to use the manual date, and data is working now, I’ll see if I can figure out why that’s happening and hopefully fix the shared template.

76

Is there an option for a quarterly budgeted item? Our homeowner’s association dues are paid once a quarter. I don’t see a quarterly option in the option in the frequency field.

77

Thanks for your help. it works.

78

There isn’t a dedicated setting, opting instead to use existing settings. Set the start date to the date of your first payment for the year, set the Frequency to Monthly, and set the Multiplier to 3.

79

Hi there.

I love the idea of this budget planner! I’ve followed the steps in the youtube tutorial but when I select “Past-Category” from the drop down list it doesn’t seem to reference any data and just fills out the monthly columns with $0.00. With my category sheet I simply add 12 months to it each year and I have just added 12 columns to start at January 2023. This means I have changed the formula that is in the installation instructions to reflect this but still getting $0.00 filled in the columns. How can I ensure that the Budget Planner references past data to fill out my category sheet for 2023?

Cheers
Brian

80

I haven’t tried it with more than 12 months of data in my Categories sheet, but I think it should work. You may have to manually change the date in M3 of the Budget Plan sheet to 1/1/23. As you mention, you’d then need to change the formula on the Categories sheet so Jan of 2023 on Categories is looking at column M on the Budget Plan sheet.

1 Like
81

Yeah, thanks! I just worked it out, I hadn’t put Jan '23 as the date in Budget Planner. Seems to be hooking now :ok_hand:t2:

1 Like
82

Another question. How do you handle budget amounts in the Budget Plan that you pay quarterly. There’s no quarterly option in the dropdown menu?

Thanks
Brian