All I want is a formatted Net worth spreadsheet that auto updates and don’t care about all the budgeting, categories, or bank transaction data. Im using excel. The Foundation template is complex and not formatted the way I want so I had hoped to use my formatted manual net worth excel spreadsheet with Tiller.
Initially I thought I understood what Tiller was doing after looking at the foundation template so after painstakingly linking all my accounts I used my existing excel spreadsheet as a foundation figuring I could just create links to the “Balance History” tab. To my surprise that tab continuously adds history as it updates so my fixed links to cell references don’t work (as they simply call out the older history entries rather than the new rows that are added upon each update). I’ve stared at the formulas used in the foundation template on the “Balances” tab (which seems intended to be a crude net worth sheet) and the complicated “LET” strings baffle me. They Include nested rowindex lookups I’m unfamiliar with to rows and columns that aren’t on the sheet (maybe the referenced rows are hidden or in another tab)? So I’m not even sure where it’s pulling the data from. for example
=LET(RowIndex,ROW($B12)-ROW($B$10),RowLookupRange,$I$4:$I$102,IFERROR(IFERROR(VLOOKUP(RowIndex,CHOOSE({1,2},RowLookupRange,O$4:O$102),2,FALSE),IF(ISERROR(MATCH(RowIndex+1,RowLookupRange,0)),“”,SUMIF($K$4:$K$102,B12,$O$4:$O$102))),“”))
What I need is a simple way to link to the latest balance of each account and failing that a tutorial to understand, cut and paste and modify the complex LET statements. I’m sure if broken down and explained the strings are not that bad?
OR a way to simply overwrite each account balances upon each update (rather than adding additional rows as the balance history does)
OR if that is too complex perhaps a way to arrange and format the balances tab in the foundation sheet (not my preferred route)
I’ve searched but cannot find what I’m looking for. Thanks in advance for hints. I’d also accept links to a simplified net worth sheet I could modify and format if anyone has done it already.
I haven’t checked the latest Excel version to see if it’s different, but I believe you can find what you’re looking for on the “Accounts” sheet. The columns may be hidden, but their should be a row for each account, and one of the columns should be the last balance.
Thanks. I was hoping I had missed something obvious. The accounts sheet is 4 columns but all data are empty on my foundation template, and I can’t find any rows to unhide. It’s maybe possible if I delete and re-create a new foundation template it might import my linked accounts into this tab? Thanks for the hint
I just downloaded the latest Foundation Template to take a look. If you grab the right side of the column D header and drag to the right, it should reveal a column “XFD”. Select both columns D and XFD, right-click and choose Unhide it will reveal all the hidden columns between them.
Holy Cow. After reloading the foundation template and playing with it my first clue was the first 4 columns apparently are optional and may need to be populated by simply using the drop down arrows. Then I saw your response and tried to drag D to the right and at first it just stretched the column. But after carefully staring at the right side of D I saw 2 vertical bars and was able to expose column XFD as you say and then unhide everything! This along with a previous post from JOLO describing how to reference the account balance on this tab using vlookup specifically something like:
=VLOOKUP(“Visa Card”,{Accounts!H2:H,Accounts!J2:J},2,FALSE)
and perhaps now I may be on my way to making this work! I have honestly never understood why programmers like to hide data and features from end users like this. (I know - we shouldn’t be mucking in there because it’s dangerous).
Yeah, something like that using VLOOKUP or XLOOKUP would be a great way to grab the data instead of directly linking to a cell since the order of the list could change if you add or remove another account.
I’ll also add – the Balances Sheet functions quite nicely as a Net Worth spreadsheet if you organize it using the Accounts Sheet into groupings that you’d like to see. Tiller organizes groups and accounts alphabetically, so to force a specific order, I like to number the groups to be the order I prefer them to appear. Then you can add any manual accounts such as real estate and vehicles that Tiller cannot bring in automatically. I haven’t found a need to deviate from a configured Balances sheet when setting up Net Worth spreadsheets in this way. Part of my hesitation to maintaining a second sheet that pulls in data from the Accounts sheet is that it adds steps to the workflow to maintain things as soon as anything changes. It may look great on Day 1, but then there are more steps for eventual things such as adding new accounts or accounts that change names, etc. and then suddenly things break down.
This syntax is what finally worked for me. although one of my test accounts is not retrieving a value returns (#N/A) so I may have to switch over to lookup by account identifier or rename my accounts for reliable lookup. I documented this in more detail on an older thread on this topic but basically this looks for the string in quotes (which identifies my account uniquely) on the Accounts tab with a range of columns F thru J and retrieves value in 5th column (which is latest balance column).
update:
I just discovered this works with the unique account IDs:
=VLOOKUP(“654d5d3077a660002f8721ba”,Accounts!$G:$J,4,FALSE)
It was failing because I was trying to “copy” and “paste” those numbers into my formulas and it wasn’t working so I foolishly used my “screenshot OCR” software from Snagit app and it was misreading 7’s as slashes so I was pasting the wrong numbers in. By using CTRL-C and then CTRL-ALT-V I was able to paste the values from the accounts sheet over to the net worth sheet formulas
Thanks for Reminder. Biggest help was uncovering the hidden cells so I marked that one. I also decided “don’t screw with any existing Tiller data” so I created a “net worth” tab and an “ID” tab. The ID tab was to periodically copy the rows in the “accounts” tab and do a “paste special” and select “values” (otherwise copy and paste tries to grab the formula not the value) so I would have a place to easily copy and paste Account IDs, which is what I decided to use in my net worth tab to pull current balances. I use 2 cells per account one for current account balance and one for the date updated. They look like this: =VLOOKUP(“65d8f862f01ad2020a49a918”,Accounts!$G:$K,4,FALSE) and =VLOOKUP(“65d8f862f01ad2020a49a918”,Accounts!$G:$K,5,FALSE). For the Range of columns G thru K an the “accounts” tab the 4th column over is Last Balance and the 5th column over is Last update so these commands extract those values from 4th over and 5th over. I have to manually copy and paste the account ID for each account I’m pulling but I decided that was more robust than unique account ID (although same amount of work either way). I also do a conditional format on the date to change it’s color if more than 3 days old so I can visually see if data is current.
, 