@heather, @KyleT, @rocketbison – I’ve also experienced this same issue. I’ve read through the thread and have the exact same issue. I’ve used the Workbook Links and changed the source. My accounts are coming through with the last balance correctly showing in column AD; but I do not have any data populating in AF:AQ or in B:M that would drive the line graphs in B:M. Any further suggestions?
@heather , @KyleT , @rocketbison — I’ve solved the err. The issue was my spreadsheet’s ‘Balance History’ columns did not match the same order as the one from the template. Thanks again!
3 Likes
I’m having the same error and I noticed my Balance History columns are not in the same order. I just downloaded the latest foundation template a few days ago. It looks like the columns are reordered and a couple of new columns have been added. I’m not sure I want to rearrange the columns, so is it possible to update the formulas in the Net Worth tracker to match what is in the latest version of Balance History?
Hi @krt - Welcome! Unfortunately I never quite understood original solution about ordering of columns in Balance History so I don’t know if changing the formula is in the answer to your issue. If you look at the formula in cell AG7 it has just a few references to the table in Balance History and it references the columns “Date”, “Account ID”, and “Balance”. It references them by name so it shouldn’t matter where they ordered in the table as long as they are within the table. If you were to highlight the first one as I did in the screenshot below, you can see a helper pop-up showing the underlying data (in this case dates in numerical format). Do you see that if you highlight that one and same for BalanceHistory[Account ID] and BalanceHistory[Balance]? You can see the BalanceHistory table by going to the named range list in the upper left by the formula bar - refer to the second screenshot. If you click that and it takes you there and you have a Date, Account ID, and Balance column with data in the range that you are trying to display then I would think that part is ok.
Thanks for the quick response. I did figure it out. Since the columns are in a different order the
SORT(FILTER(BalanceHistory[[Date]:[Account ID]]
Doesn’t work because it is a range and Account ID is now near the beginning of columns so balance doesn’t get picked up. Also, toward the end of the formula there is an index
,INDEX(tablesortedbydate,3),0),6),“”)))
That uses column numbers so those also had to change. The formula that worked for me is this:
=IF(OR(INDEX($Z$7#,1)=“”,AN2=“”,EOMONTH(TODAY(),0)+1<=AN$2),“”,LET(tablesortedbydate,SORT(FILTER(BalanceHistory[[Date]:[Account Status]],BalanceHistory[Date]<EOMONTH(AN2,0),“”),1,-1),
IFERROR(INDEX(tablesortedbydate,MATCH($AE$7#,INDEX(tablesortedbydate,3),0),8),“”)))
For reference, here are the Balance History columns that are in the version of the Foundation Template that I downloaded a few days ago:
Date, Time, Account, Account #, Account ID, Balance ID, Institution, Balance, Month, Week, Type, Class, Account Status, Date Added
2 Likes
Great job in working that out and thanks for the details. It’s interesting because the formula in AG7 for me is different. It doesn’t have that continuous range. I can see why the one you have is dependent upon the column order. My Net Worth version says v0.94. The Balance History columns in mine (downloaded months ago) are the same and same order. I suspect that maybe the formula in AG7 was updated in a later version of the Net Worth template to eliminate that dependency issue.
Here’s the formula from v0.94:
=IF(OR(INDEX($AA$7#,,1)="",AG2="",EOMONTH(TODAY(),0)+1<=AG$2),"",LET(tablesortedbydate,CHOOSECOLS(SORT(FILTER(BalanceHistory,BalanceHistory[Date]<=EOMONTH(AG2,0),""),1,-1),COLUMN(BalanceHistory[Date]),COLUMN(BalanceHistory[Account ID]),COLUMN(BalanceHistory[Balance])),IFERROR(INDEX(tablesortedbydate,MATCH(INDEX($AA$7#,,1),INDEX(tablesortedbydate,,2),0),3),"")))
1 Like
Oh interesting, I see how that would make a difference. The version on the community-templates page still points to v0.90 while the version on the tiller docs page points to the new v0.94 @randy in case you don’t already know this.