Those get pulled from columns T- V ( hidden by default). What do those show? I assume there is probably an error or it is blank. Or there may be an error on columns V
Here is the formula that the total envelope is calling. It refers to the envelopes sheet
Without seeing the whole screen it I hard to see what is showing and what isnât showing.
Are your envelopes showing up but the balance is wrong? Or are the envelopes blank too?
Is there supposed to be a list of the envelopes as well as the accounts in the balance comparison? The formula in cell T2 of the balance comparison matches what you sent.
In the envelope sheet, column BO is completely blank but there are values in column BQ that match the amount in the envelopes in column D. So it seems like the envelopes arenât referencing an account in column BO?
Ok, it looks like your sheet got messed up. Here is what the sample template sheet show in the columns
I suggest you delete your current envelope sheet and copy from the template file.
Hi Rich. I had some other things come up so it was over a week before I could get back to trying your template. My goal for initial setup was to delete your test data, then download my accounts and transactions.
- I deleted the lines in the Balance History sheet and the Funding Transactions sheet, and also all of the test transactions in the Transactions sheet.
- I then configured my categories.
- I linked my bank accounts and downloaded transactions.
- After that I categorized all transactions from the past month, just to see how things would look at that point.
- I have 3 linked accounts, so I converted the first three lines on the Accounts sheet to my accounts and deleted the additional rows of your test accounts. At that time the Accounts sheet showed me my accounts in the drop-down in column A, but when I later closed and reopened Google Sheets, column A complained that the selections were invalid.
Unfortunately, doing the above seems to have broken a few things.
On the Envelope Tracker sheet:
-
The pull-down in B4 has choices of Category, Account, and #REF!, so it lost track of something somewhere.
-
When I set B4 to Category and select a category, a little red error triangle appears in G3 (Current Envelope Balance) and the error is, âDid not find value âGroceriesâ in VLOOKUP evaluation,â where "Groceries is the name of the category.
-
When I set Additional Filters to Show All Transactions (cell C5), both B7 and F7 show #VALUE!. When I set C5 to Show or Hide Envelope Fundings, I see the Date/Description/Note headings, but no transactions.
On the Categories sheet:
- The cells in the Funding Account column complain about an invalid range, and there are no choices except âAccountâ when I click the drop-down.
That column seems to point to the Balance Comparison sheet. It is blank from row 7 down.
The Accounts sheet now says that the drop-downs in column A are invalid, and it doesnât show any accounts for me to choose from.
So it seems like trying to replace your test data with my real data really broke some things. Any suggestions on how I can fix this?
Thank you,
Steve
Hello,
Iâm having trouble figuring out which accounts go into calculating this formula. How do I set this?
For example, I donât need transactions relating to my house-value account being calculated as needing to go into an envelope. I see there already is some logic somewhere, as if I add a manual transaction under my mortgage account this area in the envelope sheet doesnât update, but it does when I add a transactions for my checking account. This is the expected behavior. Where do I set this though? Which accounts I want to allocate to envelopes. Thank you.
in the categories sheet just hide the categories that you donât want to be part of the envelope system.
you can do this by checking the box in column F
But basically, I take any transaction that is marked with the type income and subtract and transaction that is an expense.
I hide transfers, investments, I also mark mortgage details ( principal vs interest) and only track to the items coming from my checking/savings accounts.
I mark those like this
1 Like
Hi I am very interested in using this spreadsheet but I am lost in getting it set up.
Specifically I donât understand why the transactions that have imported do not have their respective category assigned to them that has already been done. How do I import the categories from the Foundation Template to this categories sheet in 2.0?
Thanks!
Kyle, I think so am following you. This envelope sheet is comparable with the foundation template. And category sheet. You will need to add a few columns to your existing category sheet. You can copy right from my sheet to yours, they wonât impact your other Tiller sheets.
1 Like
Ok good news. I was able to link Tiller to this sheet instead of trying to import it to my Tiller sheet. It does work. Everything appears to work correctly. However, before I have funded any envelopes I have an existing envelope balance on one of my accounts. I cannot figure out why the total envelope balance on my bank account shows a balance when I have not funded any envelopes? Should it be like this?
So any transactions that you have will take away money from your envelopes, Funding will add money to them.
I am assuming the envelope balance is negative?
You will need to set the initial balance so you have money to fund the envelopes you can follow these steps to do this. ( note the colors and stuff have changed but the process is the same)
Let me know if that works for you.
Rich,
Thanks so much for trying to help me.
Iâm on day 2 of setting this thing up and I love it so much I want to get it working.
I read your documentation from the 1.7 version and your forward about your history with different softwares is exactly my story. I wish I had the programming knowledge you do when it comes to spreadsheets!
Right now my problem is that my envelope funding message says that I have money to allocate (positive number) when I have not manually added the beginning balance transaction.
I have confirmed that the total of the dollars left to allocate matches exactly the dollar amount of the sum of all my paycheck income.
I have checked the box to exclude the income category from the envelopes, but this balance shows up still.
What am I missing?
Thanks again so much!
Kyle
Ok I got it. Any transaction that is marked as an income category ( paycheck) will add money to be funded. I assume you marked your paychecks as paychecks which gave you a positive amount. Make sense?
Yes! I think I was not thinking about the current month and the fact there is already income being counted for the funding. So I SUBTRACTED the current month income from the bank balance and ADDED the negative envelope balance and voila! I hope this helps someone else that gets confused.
Awesome I am glad you got it worked out. Let me know if you have any questions
Oh man last night was awesome! I re-grouped the budget and funded all my money and just canât thank you enough for sharing this! I went so far as to start a AppSheet app with just basic interface to the spreadsheet and it works! I wanted a mobile way to assign categories to transactions that donât catch in my AutoCal. Eventually I need to figure out how to split transactions in the app and iâve briefly read about that in some other topics. But about EnvelopesâŚ
I think the last tweak I would like which I do not know how to achieve at this time is to show the monthly income status listed with the rest of my envelopes. But when I do this the balance then adds the income to the total balance comparison.
I realize itâs not an âenvelopeâ to fund, but if there was just some way to visually know where you are at funding wise for the month compared to your monthly income budget it would be nice.
Thanks again Rich!
1 Like
Ok new question. I tried using the sweep function and when I just chose one category which had a surplus of $100 and I clicked âyesâ to perform the sweep. But the category balance changed to $0 and no entries were added to my funding transactions tab. But my envelope balance is still correct. Under the envelope funding header it still says âGreat job! All the money has been allocatedâ. So Iâm lost as to what happened?
So what is supposed to happen is all categories enabled to be swept, ( in categories sheet). will fund/defund the envelope so the balance goes to zero. (notice the "Fund Amount "Column.
When you go to Action - Fund Envelopes it will write these entries into theFunding Transaction Sheets.
Also depending on the amounts you should have money available to fund. In my example it the $4013.58
You may need to sort the funding transactions sheet to see the latest entries. youcan also search for sweep as the code changes the description for sweep entries.
If you still dont see these, let me knwo and we can dive into it more.
Did you get this resolved? I hope you are enjoying the envelope sheet