Category tracker issue -Transaction Description Sum By Category no longer updating

Google Sheets
1

First time poster, thank you in advanced for any support or guidance.

Been using tiller money for a few months now, I’m using the Zero Sum template (Envelop Budget). I use the “Category Tracker” spreadsheet from Labs when i consolidate my budget at the end of the month. However since yesterday the section to the right “Transaction Description Sum By Category” No longer updates when I select a “category”.

I don’t believe I change anything (At least not in purpose). I tried Reloading the sheet, and that didn’t work.

Any other ideas welcome.
Thanks
Matt

2

Do you see any errors or REF on the sheet. This can give us a clue into which formula busted. if you do see any send a screenshot

3

No errors, REF or any indication of anything off. I have 2 other sheets with similar setup and I compared the formula side by side and they seem identical.

The left side (Category Amount Summary) works as expected.

url: //ibb.co/VCrjLB1
4

The first think I woudl check is teh formulas on Colums I ( you have to unhide these columns. If there are any errors or if the letters are not showing. just copy the formula from teh cell and repaste it right back. Its an odd Google glitch.

image

If all looks good with that …

CAn you compare the formulat in cell D7 to this one and see if anything is different?

=iferror(QUERY(Transactions!A:AE,“SELECT " &I1& " , SUM(” &I3 & “) WHERE " &I4& " >= DATE “”” & text(E4, “yyyy-mm-dd”) & “”" AND " &I4& " <= DATE “”" & text(E5, “yyyy-mm-dd”) & “”" AND " &I2& " = ‘" & E3 & "’ GROUP BY " &I1& " ORDER BY " &I1& " ASC LABEL SUM(" &I3 & “) ‘’, " &I1& " ‘’”, -1),"")

5

Ok Yeah, there is an error on cell I1
It reads: “#N/A” and the error is: “Did not find value ‘Description’ in MATCH evaluation”

D7 look just like what you posted.

6

So for I1, cut the formula and repaste rigth back in the cell
do this for any items with the error.

7

no, It doesn’t do anything, it show the same error

8

Is this the formula you are seeing?

=REGEXEXTRACT(ADDRESS(1,MATCH(H1, indirect("’"&G1 &"’!$A$1:$1"), 0)), “[A-Z]+”)

9

Yes

“=REGEXEXTRACT(ADDRESS(1,MATCH(H1, indirect(”’"&G1 &"’!$A$1:$1"), 0)), “[A-Z]+”)"

Screenshot: / /ibb.co/3M5hvZq

10

ok, so delete the formula, then hit enter and re-add it back. This is the source of the issue. It shoudl resolve teh answer “C” which should be the description Column on your transactions sheet.

Just to confirm you could just type in C to I1 and see if it works, then put the formula back so it is more dynamic if columsn change

11

Putting a “C” works, but the formula would not work. I press Enter after I deleted it, copy paste from here, copy paste it from another sheet, drag it from line 2, all end up showing the same #N/A error.

12

you can try this formula as well. Its basically teh same thing. look up the description category and give me the letter back

=char(64+match(H1,INDIRECT("’"&G1&"’!$A$1:$1"),0))

13

The new Formula yield a #REF
"Function INDIRECT parameter 1 value is “Transaction’!$A$1:$1’. It is not a valid cell/range reference”

This made me look at the Transaction sheet and found out that I must had accidentally replaced the “Description” header with a " ’ ". Once corrected, the original formula works. (the new formula still give #REF).

thank you for the support =)

1 Like
14

Glad you found that! It’s usually the first thing I ask people to look for. It’s common to see errors that don’t get fixed by a restore via Tiller Money Labs if you’re missing a header keyword like Date, Description, Category, or Amount on the Transactions sheet or the header keyword Category on the Categories sheet.