OK! I got it!!! Thank you. Next question. Why can’t a category name be the same as a group name on the categories sheet? I’m getting a Bad Groups because some are the same name.
In general, that can cause issues with reports.
Glad you’ve got it working! 
1 Like
I updated the instructions based on your experience to hopefully improve the setup going forward for others. Thanks for the feedback. 
1 Like
QQ - Do you know how to build a conditional formatting formula to highlight any cells from the category column that are not from col A of the category sheet?
Give this a try and let me know how it goes. It will also highlight blank Category cells.
=NOT(REGEXMATCH(D2, "^"&TEXTJOIN("$|^", TRUE, INDIRECT("Categories!A2:A"))&"$"))
It assumes the Transactions sheet Category column is D and Categories sheet Category column is A.
1 Like
I have added additional conditional formatting instructions to the Setup for each check.
I also made the following refinements to the Data Checker tool formula:
For Bad Groups, now checks Category matches any group name, not just the one on the same row.
Added individual check on/off control in 2nd row parameters.
Some minor formula adjustments.
Sounds useful, but wish this was a “managed solution” rather than all the manual edits that could get messed up or accidentally lost later.
The one formula is all that is really needed. The conditional formatting is more of a visual aide. Thanks for considering it
Hello,
I tried this formula in A1 of my Transactions Sheet. I get a circular reference error. Any ideas why?
thanks
Does the error provide additional info?
It might mean you have another header cell in your Transactions sheet referencing the header row.
This is the full error
#REF
Circular dependency detected. To resolve with iterative calculation, see File > Settings.
I don’t have any header row references. - they are all just plain text,
But I have added columns to the Transactions sheet, and rearranged a lot of them.
So, you haven’t added a Group column that uses ARRAYFORMULA, for example?
Do you still get the error if you create an empty sheet and copy/paste the formula into it’s A1 cell?
No Calculated columns
I created a new sheet and put the formula in A1 and it now displays “1990” and nothing else in cells underneath.
Not sure what that means!
It’s the number of uncategorized transactions. You may have a bunch of empty rows at the bottom of your Transactions sheet.
So, you said you put the formula in the Transactions sheet A1 cell?
What column letters are the Date and Category columns?
The formula references Transactions!B1:Z1
If the formula was entered into a column before the Date and Category columns within that B1:Z1 range, that would cause a circular reference error.
The A1 cell should prevent that from happening.
Date is in B and Category is in D
Yes, I put the formula in Transactions A1
What happens if you change the two occurrences of:
Transactions!B1:Z1
to:
Transactions!B1:D1
(change Z1 to D1)
OK, changed the Z1 to D1 in the two lines - I am still getting the circular Reference Error
Hi,
Got it figured out. It was my bad, not yours. My Tiller Foundation sheet is a dogs-breakfast of modifications and additions. When I fixed MY problem the formula worked perfectly.
Thanks for your help.
Jim
1 Like
Thanks for the update @jemmoa7 !
Thank you @Mark.S for your Data Checker Tool. I found three Bad Cats and one Bad Act Groups. Your utility is the type of tool that really helps a tool like Tiller Money. I think it would be a big value add if it was someday integrated into the template or the Tiller Money Feeds options. I believe it would really help new users a lot to immediately find data quality issues in their template as they are getting on-boarded.
1 Like