Monthly Budget Tab: budget vs actual not populating

For some reason the monthly budget tab is no longer displaying my planned expenses versus actuals. The transactions tab is fine, but I’m not sure if there’s a glitch somewhere.

Is this with Google Sheets or MS365 Excel?

I use Google Sheets.

Try copy/pasting this Data Checker into an empty cell and let me know what you find:

=LET(lastRow, "", alertsOn, TRUE, alertsStrict, TRUE, alertsHide, "name1;name2", tool, "Data Checker", v,"1.0", header, tool,
cCats, INDIRECT(LET(ltr,CHAR(64+XMATCH("Category",INDIRECT("Categories!A1:Z1"))),"Categories!"&ltr&"2:"&ltr)),
cGroups, INDIRECT(LET(ltr,CHAR(64+XMATCH("Group",INDIRECT("Categories!A1:Z1"))),"Categories!"&ltr&"2:"&ltr)),
cTypes, INDIRECT(LET(ltr,CHAR(64+XMATCH("Type",INDIRECT("Categories!A1:Z1"))),"Categories!"&ltr&"2:"&ltr)),
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)),
acctIds, INDIRECT(LET(ltr,CHAR(64+XMATCH("Account Id",INDIRECT("'Accounts'!F1:Z1"))+5),"Accounts!"&ltr&"2:"&ltr)),
acctHides, INDIRECT(LET(ltr,CHAR(64+XMATCH("Hide",INDIRECT("'Accounts'!F1:Z1"))+5),"Accounts!$"&ltr&"2:"&ltr)),
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),
badCatsCnt, COUNTA(IFNA(FILTER(tCats,ARRAYFORMULA(NOT(REGEXMATCH(tCats,"^"&TEXTJOIN("$|^",TRUE,cCats)&"$"))),tCats<>""))),
badDatesCnt, ARRAYFORMULA(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,IMPORTRANGE(alertsUrl,"Dashboard!A5:A"),alertDescs,IMPORTRANGE(alertsUrl,"Dashboard!H5:H"),
       institutions,IF(alertsStrict, institutions1, IFERROR(FILTER(institutions1,alertDescs<>"Site Temporarily Unavailable"),"")),
       BYROW(SORT(UNIQUE( FILTER(acctInsts,NOT(ISNUMBER(FIND("manual:",acctIds))),acctHides<>"Hide",acctInsts<>"") )),
       LAMBDA(institution,
             REGEXREPLACE(TEXTJOIN(";", TRUE, IFNA(FILTER(institutions,SEARCH(institution,institutions)))),"\s*\([0-9]+\)","")
             ))),),
alertsFlat, IFERROR(TRANSPOSE(SPLIT(TEXTJOIN(";", TRUE, alertsResults), ";")),),
alerts, SORT(UNIQUE(IFNA(FILTER(alertsFlat,LEN(alertsFlat)>0,NOT(ISNUMBER(SEARCH(alertsFlat,alertsHide))))))),
alertsCnt, COUNTA(alerts),
IF(badCatCatsCnt > 0, {HYPERLINK(toolUrl,"Bad Cat Cats");badCatCats},
IF(badGroupsCnt > 0, {HYPERLINK(toolUrl,"Bad Groups");badGroups},
IF(badTypesCnt > 0, {HYPERLINK(toolUrl,"Bad Types");badTypes},
IF(uncatsCnt > 0, uncatsCnt, 
IF(badCatsCnt > 0, {HYPERLINK(toolUrl,"Bad Cats");SORT(UNIQUE(IFNA(FILTER(tCats,ARRAYFORMULA(NOT(REGEXMATCH(tCats,"^"&TEXTJOIN("$|^",TRUE,cCats)&"$"))),tCats<>""))))},
IF(badDatesCnt > 0, {HYPERLINK(toolUrl,"Bad Dates");SORT(UNIQUE(FILTER(IF(tDates="", "<blank>", tDates), ISERROR(DATEVALUE(TEXT(tDates, "mm/dd/yyyy"))) + (tDates = ""))))},
IF(badAcctGrpsCnt > 0, {HYPERLINK(toolUrl,"Bad Act Groups");badAcctGrps},
IF(alertsCnt > 0, {HYPERLINK(alertsUrl,"Alerts");alerts},
HYPERLINK(toolUrl,header))
)))))))
)

Mark, when I copy this into any cell below the range, it gives me an error message up top. It says, “Array result was not expanded because it would overwrite data in [cell number.]”

Yes, it needs an empty column for the results. I like putting it in the Transactions sheet A1 cell, because it’s nice and visible, along side some of the data it’s checking.

Hi @Mark.S , it returned a value of $118.

Incidentally, is there anyone in the community who’d be willing to consult for a fee to look at this with me in real time?

What is the name of the header when it returns $118?

It’s cell A1 in Transactions.

Gotcha - what is the name in cell A1?

For example, Bat Cats is the name here.
That name changes depending on what the issue is, so that’s what I’m trying to find out to know what to do next.
It’s also typically called a header (top row).
Screenshot - 20240523_162216

It was only the image link for Tiller’s logo.

I see. Select cell A1 and hit the Delete key, so there is no image and no formula, completely empty.
Then copy the formula from this thread using the upper-right copy icon
image
Double-click the A1 cell to get a blinking cursor.
Then paste the formula Ctrl+Shift+V
Report back with the tool results :slight_smile:

Yes, that’s what I did before and again now. Still getting 118 as a result. Tried the same in Cell A1 in the Monthly Budget worksheet, and the same number appeared.

Okay, but can you see the name above 118 now? A screenshot always helps :slight_smile:

Here is what I see…and, as of this morning, the value is 122.

Interesting. Could you please select the A1 cell with the 122, so that the formula displays in the formula bar (fx), and resend that snapshot?

Oh, actually, that’s the number of uncategorized categories - blank Category column cells on the Transactions sheet. So, assign categories to those and report back with the new tool results. You could find them by filtering the Category column for (Blanks). It could be blank rows at the bottom of the sheet - if so, delete those blank rows.

When you initially indicated $118 (with a dollar sign), I was expecting a different type of result and so my mind was focused on that and not thinking it would be the uncategorized category count.