Hi Community,
I have made a new Sheet Tab to track balances by account, and I am trying to reference accounts listed on the Balances Tab. However, the balances move up and down rows based on when the particular balance was updated. Is there a way I can reference the current balance on the Balances Tab in another Sheet Tab and it will be relative, meaning if the account/balance moves up and down on the Balances Tab it will still reference correctly on my new Sheet Tab.
I am guessing there is a way to do this but it’s beyond my primitive spreadsheet capabilities.
Thanks very much.
ohhhh i love this idea! Please someone help @zebra22 in hopes that there will be sharing of tab upon success!!
I would try using the Balance History sheet for this, and then you can do a filter, or ifs, to only return a specific account.
1 Like
This is the answer, as Balance History is the source of truth for the Balances sheet via the Accounts sheet and will let you report on balance over time if desired.
You can play around with this formula:
=sortn(sort(D2:M9999,2,false),9^9,2,1,true)
You have to combine the date and time into one field. That should be column D, column E is the account.
Here’s how to combine the date/time:
=concatenate(text(B2,"mm/dd/yyyy")&" "&text(C2,"hh:mm:ss"))
Great question, @zebra22. As @cculber2 mentioned, we built the Accounts sheet to aggregate account data from the Balances History sheet for use by all downstream templates needing account and balance information. The hidden columns to the right on the Accounts sheet are perfect for what you are doing if you want to identify unique accounts, information about each account, and last balances.
Adding and deleting accounts will still change the account order in the Accounts sheet so I’d recommend using a VLOOKUP() on the Account Id.
Thank you so much everyone…Randy, might you able to be more specific on how to use the VLOOKUP() with the account’s Account ID? I am hoping to be able to pull the most current Balance of each account off the Balance History into this new Sheets Tab.
I’d use a formula like this:
=vlookup("EXACT ACCOUNT NAME HERE",{Accounts!$H$2:$H,Accounts!$J$2:$J},2,false)
This version 
uses the account name.
If you were to use the Account Id, you’d copy out the Account Id (e.g. “5f35e23d2f0462001e1e3df3”) and then put that in the first text field and replace $H$2:$H with $G$2:$G in the formula. The more I think about it, using the Account Name (instead of ID) is fine so long as you don’t have account names that aren’t unique.
Try the Balance History Tracker I just created for some inspiration.
1 Like
Hey @yossiea, I’m curious why the recurring expenses sheet is in your “Shared Tiller Sheets” file here? I’d recommend hiding or removing that one so it’s not confusing to folks as the Recurring Expenses sheet is installed/restored/managed through the Tiller Community Solutions add-on.
Hi, this was my addition to the template to add more filtering and grouping capabilities.
Oh I see, might want to take Adam B’s name off your version then? or otherwise make it clear it’s a iteration of that one?
Will do and I changed the sheet name.