Problems with Monthly Budget, Yearly Budget, and Debt Payoff

Microsoft Excel
1

In Excel, I have setup my ‘Categories’ with the correct budget amounts (there are 37 categories in total) which do show up on the monthly and yearly budgets but the actual and available do not populate properly on about half of them.

Several accounts I have setup populate on the Debt Payoff but do not populate the actual or available on the budgets.

I have tried to refresh the budget sheets and things only disappear differently when I do that. There are formulas in the cells in question.

I did search the topics but did not find info on how to fix this issue I have.

2

The Monthly and Yearly Budget sheets are populated by the budgets you set in the Categories sheet and the actual spending based on your Transactions sheet once you’ve categorized your transactions.

Can you please confirm you’ve categorized all of your transactions and you’re viewing the correct time period in the template that corresponds with your budget amounts and your categorized data?

Have you made any edits directly to the templates you’re not seeing data in? Are there any errors in your spreadsheet?

3

Try copy/pasting this Data Checker Tool into an empty sheet to check for common data issues:

=LET(lastRow, "", alertsOn, TRUE, alertsStrict, TRUE, alertsHide, "name1;name2", tool, "Data Checker", v,"1.1", header, tool,
lastRow_, IF(lastRow<>"",lastRow,ROWS(Transactions[Date])+1),
cCats, Categories[Category], cGroups, Categories[Group], cTypes, Categories[Type],
tCats, INDIRECT(LET(ltr,CHAR(64+XMATCH("Category",INDIRECT("Transactions!B1:Z1"))+1),"Transactions!"&ltr&"2:"&ltr&lastRow_)),
tDates, INDIRECT(LET(ltr,CHAR(64+XMATCH("Date",INDIRECT("Transactions!B1:Z1"))+1),"Transactions!"&ltr&"2:"&ltr&lastRow_)),
acctInsts, INDIRECT(LET(ltr,CHAR(64+XMATCH("Institution",INDIRECT("'Accounts'!F1:Z1"))+5),"Accounts!"&ltr&"2:"&ltr&"300")),
acctIds, INDIRECT(LET(ltr,CHAR(64+XMATCH("Account Id",INDIRECT("'Accounts'!F1:Z1"))+5),"Accounts!"&ltr&"2:"&ltr&"300")),
acctHides, INDIRECT(LET(ltr,CHAR(64+XMATCH("Hide",INDIRECT("'Accounts'!F1:Z1"))+5),"Accounts!"&ltr&"2:"&ltr&"300")),
acctClass, INDIRECT(LET(ltr,CHAR(64+XMATCH("Class",INDIRECT("'Accounts'!F1:Z1"))+5),"Accounts!"&ltr&"2:"&ltr&"300")),
acctGroups, INDIRECT(LET(ltr,CHAR(64+XMATCH("Group",INDIRECT("'Accounts'!F1:Z1"))+5),"Accounts!"&ltr&"2:"&ltr&"300")),
badCatCats, IFERROR(SORT(UNIQUE(FILTER(IF(cCats<>"",cCats,IF((cGroups<>"")+(cTypes<>""),"<blank>","")),(COUNTIF(cCats,cCats)>1)+((COUNTIF(cTypes,cCats)>1)*(cTypes<>"Transfer"))+((cCats="")*((cGroups<>"")+(cTypes<>"")))))),""),
badCatCatsCnt, SUM(--(badCatCats<>"")),
badGroups, IFERROR(SORT(UNIQUE(FILTER(IF((cGroups = "") * (cCats <> ""), "<blank>", IF(cGroups=cCats, cGroups, "")), (cGroups = cCats) + (cGroups = "")))),""),
badGroupsCnt, SUM(--(badGroups<>"")),
badTypes, IFERROR(SORT(UNIQUE(FILTER(IF(cTypes<>"",cTypes,"<blank>"),(cCats<>"")*(NOT(EXACT(cTypes,"Income")))*(NOT(EXACT(cTypes,"Expense")))*(NOT(EXACT(cTypes,"Transfer")))))),""),
badTypesCnt, SUM(--(badTypes<>"")),
uncatsCnt, COUNTBLANK(tCats),
badCatsFlags, --(MMULT(--EXACT(tCats, TRANSPOSE(cCats)), ROW(cCats)^0)=0) * (tCats <> ""),
badCatsCnt, SUMPRODUCT(badCatsFlags),
badDatesCnt, SUM(IF(ISERROR(DATEVALUE(TEXT(tDates, "mm/dd/yyyy"))), 1, IF(tDates = "", 1, 0))),
badAcctGrps, IFERROR(UNIQUE(FILTER(acctGroups, (acctGroups<>"") * (COUNTIFS(acctClass, "<>"&acctClass, acctGroups, acctGroups)+(acctGroups="") * (COUNTIF(acctClass, "<>"&acctClass)+COUNTIF(acctGroups, "")>1)) )),""),
badAcctGrpsCnt, SUM(--(badAcctGrps<>"")),
alertsUrl, "https://docs.google.com/spreadsheets/d/1_eD-uLs0FVM5BVCs5OGIYXqDbJaptH-BSDGzZy_C3v0/edit#gid=181597333",
toolUrl, "https://community.tillerhq.com/t/data-checker-tool/25582#list-of-checks-5",
alertsResults,IF(alertsOn, LET(institutions1,INDIRECT("'Institution Alerts'!A5:A1000"),
       alertDescs,INDIRECT(LET(ltr,CHAR(64+XMATCH("Description",INDIRECT("'Institution Alerts'!A4:Z4"))),"'Institution Alerts'!"&ltr&"5:"&ltr&"1000")),
       institutions2,IF(alertsStrict, institutions1, IFERROR(FILTER(institutions1,alertDescs<>"Site Temporarily Unavailable"),"")),
       institutions,IFERROR(TRIM(LEFT(institutions2, SEARCH(CHAR(9), SUBSTITUTE(institutions2, "(", CHAR(9), LEN(institutions2) - LEN(SUBSTITUTE(institutions2, "(", ""))))-1)),institutions2),
       BYROW(SORT(UNIQUE(FILTER(acctInsts,NOT(ISNUMBER(FIND("manual:",acctIds)))*(acctHides<>"Hide")*(acctInsts<>""),""))),
       LAMBDA(institution,
             IFERROR(TEXTJOIN(";", TRUE, FILTER(institutions,ISNUMBER(SEARCH(institution, institutions)),"")),"")
             ))),),
alertsFlat, IFERROR(TRANSPOSE(TEXTSPLIT(TEXTJOIN(";", TRUE, alertsResults), ";")),""),
alerts, SORT(UNIQUE(FILTER(alertsFlat,(LEN(alertsFlat)>0)*(NOT(ISNUMBER(SEARCH(alertsFlat,alertsHide)))),""))),
alertsCnt, SUM(--(alerts<>"")),
IF(badCatCatsCnt > 0, VSTACK("Bad Cat Cats",badCatCats),
IF(badGroupsCnt > 0, VSTACK("Bad Groups",badGroups),
IF(badTypesCnt > 0, VSTACK("Bad Types",badTypes),
IF(uncatsCnt > 0, uncatsCnt,
IF(badCatsCnt > 0, VSTACK("Bad Cats",SORT(REDUCE(,FILTER(tCats,badCatsFlags,""), LAMBDA(a,v, IF(SUM(--EXACT(a, v)), a, VSTACK(a, v)))))),
IF(badDatesCnt > 0, VSTACK("Bad Dates",SORT(UNIQUE(FILTER(IF(tDates="", "<blank>", tDates), ISERROR(DATEVALUE(TEXT(tDates, "mm/dd/yyyy"))) + (tDates = ""),"")))),
IF(badAcctGrpsCnt > 0, VSTACK("Bad Act Groups",badAcctGrps),
IF(alertsCnt > 0, VSTACK("Alerts",alerts),
header)
)))))))
)
1 Like