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?
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