Correct, this hasnāt been submitted to the gallery yet, so you can download from the link in the first post.
1 Like
Another dumb questionā¦I have REF errors in Prior Budget and Current Available columns when setting to 1/1/23. Is it supposed to be that way and it will correct itself as 2023 Transactions start hitting? Iām assuming this is a Month 1 issue when starting a new year because there is nothing to feed from yet.
Is that in the new version I just released? I tweaked a setting in the new version that should stop that from happening, but maybe I got it wrong.
Yes, itās the new version.
If you look at CV33, is it blank, or does it show a range there? Since weāre starting fresh, there shouldnāt be a prior budget period, so the formula is supposed to set it to be blank.
Has the following:
=IF(CU30=CU29,āā,$CR$21&ā!$ā&CU29&ā$2:$ā&CU31)
That looks correct, so not sure why youāre getting an error. If you wanted to share your template with me via a direct message I could try and figure out whatās going on.
Iām seeing the same behavior as @YouBet96. If I set the ābudget startā to 1/1/2023 then many columns have ā#REF!ā errors in each cell. If I set it to 12/1/2022 then the #REF! is replaced with actual numbers. On my sheet CV33 is blank (unlike @YouBet96ās).
I have historical 2022 budget in the Categories sheet, and I have 2023 budget there as well. One major difference between the two is that the historical numbers are actual numbers while the 2023 budget values are derived from your Budget Plan worksheet. I donāt believe this is a contributing factor though. Iāve tried replacing all the formulae with the derived values and still get the #REF! error.
Deleting 2022ās budget columns does not resolve the issue.
The actual error in the #REF! cells is:
Error
Function INDIRECT parameter 1 value is āā. It is not a valid cell/range reference.
Ahh, found the error. One of the IFERRORās didnāt get copied to the shared template. Iāve updated the shared template and increased the version number to 1.20. Give it another try and let me know. Thanks for helping me find this @YouBet96 and @jmccabe !
1 Like
Iāve updated the shared template and increased the version number to 1.20
Pure pedantry, of course, but you appear to have revised from āVersion 1.10ā to āVersion 1.2ā, not āVersion 1.20ā.
Pedantry aside, the update looks successful. Itās accepting ā1/1/2023ā and various future dates as valid.
Thanks for this template! Iām still trying to wrap my head around what itās actually telling me and how I might use that data for future insights, but so far itās looking like itāll bring me a lot of value once I figure it out. 
Just update the version number, thanks. Glad itās working again as it should. For me, the focus is to point out which categories Iām getting close to the budget limits, and which pools/accounts might be low in funds due to budgetary promises.
T hree questions:
-
To update spreadsheet version, do we just copy your worksheet into our google sheets workbook and then copy paste the ābudget poolsā, ā āFund Pools with Accountsā, and āCategory Transfer Toolā sections into the updated worksheet?
-
For credit card balances, is this accounted for in the āavailable funds in poolsā calculation? To put another way, I donāt have to figure out what my credit card balance is and subtract that from the available funds in pool do I?
-
Is col AM supposed to change to the current month? The worksheet still says āAvailable in Decemberā.
Thanks.
Yes, until itās available in the Gallery, updating works as youāve laid out.
Credit card transactions would deducted/added from your accounts as they occur, so the currently available will reflect those transactions.
Woops, AM should just say āCurrently Availableā, I just updated the shared template to reflect this. You can just type āCurrently Availableā in that cell, and change the formatting so the text wraps. Thanks for pointing that out!
Is it possible to hide categories that are not aligned to a pool? I have several that I no longer use and have hidden from the main budget tabs. Iād like to hide those and the income categories on the Budget Status tab, along with their group (if everything in the group is hidden). Thank you!
Good idea. Iāll look at adding that to the template. For now, if you update cell K5 to the following formula it should take care of it for the Categories section:
=SORT(FILTER(AW5:BI,AW5:AW<>"",AX5:AX<>"Transfer",AZ5:AZ<>""),IF($A$22="Percentage",12,11),TRUE)
Iām not sure I understand because credit card accounts cannot be added to a āpoolā (due to the cellās data validation), so any credit card debt would not be subtracted from the available money in the bank accounts. Sorry if Iām misunderstanding something.
You are welcome. Glad we caught it!
1 Like
As long as the transactions that are charged against the credit card account are in a category that has a pool assigned, then they will count against that pool. For example, if you buy groceries and pay with your credit card, as long as you assign the āGroceriesā category to the transaction, and you put the āGroceriesā category in a Pool where the funds to pay the credit card come from (eg. Primary checking account), then the grocery transaction will count against that Pool of funds. The credit card in this (and most) example is just a middle-man for transferring your bank account funds to the grocery store, which is why they canāt be a source for a Pool of funds.
With regards to starting from a new year, I did change to 1/1/2023, however the prior period is looking at 12/1/2022 Iām assuming. If I am starting new, should that be 1/1/2023 until we get to March, in which case it should become 2/1/2023?
Iām not certain on how to best handle that. In the latest update to the shared template I set it to not look at prior budget if itās the start of a new budget year, assuming one would want to have a āclean slateā, but Iād be interested in hearing feedback on how others think that should behave.