I’m looking to populate the balances from the balances sheet into a new sheet I created however when I add a new account or the sheet updates sometimes the order of the accounts in the columns changes, which changes the values in my custom sheet since the values are now linked to a specific cell but now there is a different account in that cell. I successfully was able to use the query function to only select the value associated with the right account for the assets column. However I got an error message in the liabilities column since there was no data there despite my ability to see the data on the spreadsheet. How do I query the liabilities portion of the balances sheet in the foundation template?
@jon.winston7, I’d recommend querying the Balance History sheet rather than the Balances sheet as that is the raw data source and should give you better results.
Otherwise, I’m not totally sure, but maybe this thread would help or check out the solutions in Tiller Money Labs as there might already be something built for what you’re trying to do.
Heather
This is a common problem, @jon.winston7. The Balance History data is consolidated in a hidden area on the right side of the Accounts sheet. I recommend unhiding this area and writing your formulas to reference this data set (it drives the Balances sheet too). It is much more stable as a data reference than the formatted Balances sheet.
Let me know if you have any more questions.
1 Like