Finding Invalid Categories on Transactions Sheet

Mark.S · December 13, 2023, 10:15pm

Overview

You probably have randomly noticed this little red triangle and corresponding error message on a Category within the Google Sheets Transactions sheet (or Data Validation Error triangle in Excel):
“Invalid: Input must fall within specified range”

Transactions sheet

This category is invalid, because it is not found on the Categories sheet. You may simply fix this occurrence by changing the Transaction sheet Category to match an existing Categories sheet Category, and hope to just move on with your day.

… meanwhile, in the back of your mind …
… how did that happen? … is the rest of my data okay? …
… what if there are more of them? …
… how do you find them all with thousands of transactions? …

Not to worry, a couple formulas make them easy to find.

Installation

The new formulas get added to the Spending Trends sheet that is already included by default with a new Tiller Foundation Template spreadsheet.

If your spreadsheet does not have the Spending Trends sheet, install it from the menu:
Extensions > Tiller Money Feeds > Templates > Spending Trends

What if I do not want to use/modify the Spending Trends sheet?

Setup

Google Sheets

Modify the Spending Trends sheet using these steps:

Copy cell G8, Paste-special-Format-only into cell H8 (Format only)
Copy/Paste-special-Values-only the following formula into cell H8 (Values only):

=COUNTA(IFNA(FILTER(INDIRECT(AA20),ARRAYFORMULA(NOT(REGEXMATCH(INDIRECT(AA20),"^"&TEXTJOIN("$|^",TRUE,INDIRECT(AA23))&"$"))),INDIRECT(AA20)<>"")))

Right-click cell H8 > Insert note, copy/paste the following text:

Invalid Transaction Categories.
See unique list in Cell L33
You may need to open the column group above
the column letters (a plus sign box), to see column L.

If column L is not visible, click + sign above column J to expand the Group to make column L visible.
Copy/Paste-special-Values-only the following text into cell L32 (Values only):
Invalid Transaction Categories:
Copy/Paste-special-Values-only the following formula into cell L33 (Values only):

=SORT(UNIQUE(IFNA(FILTER(INDIRECT(AA20),ARRAYFORMULA(NOT(REGEXMATCH(INDIRECT(AA20),"^"&TEXTJOIN("$|^",TRUE,INDIRECT(AA23))&"$"))),INDIRECT(AA20)<>""))))

Set Text wrapping to overflow for cells L32 and below, if not already set that way.

Details

The new formulas leverage/use the Spending Trends sheet helper columns that dynamically handle Transactions/Categories sheet column locations that are different from the Tiller Foundation Template defaults.

Cell	Value
AA10	Start Date
AA11	End Date
AA18	Transactions Date
AA20	Transactions Category
AA23	Categories Category

Microsoft Excel

Modify the Spending Trends sheet using these steps:

Copy cell G8, Paste-Formatting into cell H8 (Format only)
Copy/Paste-Values the following formula into cell H8 (Values only):

=SUMPRODUCT(
  --(MMULT(--EXACT(Transactions[Category], TRANSPOSE(Categories[Category])), ROW(Categories[Category])^0)=0) *
  (Transactions[Date] >= AA11) *
  (Transactions[Date] <= AA12) *
  (Transactions[Category] <> "")
)

Right-click cell H8 > Insert note/comment, copy/paste the following text:

Invalid Transaction Categories.
See unique list starting in Cell L34
You may need to open the column group above
the column letters (a plus sign box), to see column L.

If column L is not visible, click + sign above column J to expand the Group to make column L visible.
Copy/Paste-Values the following text into cell L33 (Values only):
Invalid Transaction Categories:
Copy/Paste-Values the following formula into cell L34 (Values only):

=SORT(REDUCE(,
FILTER(Transactions[Category],
  --(MMULT(--EXACT(Transactions[Category], TRANSPOSE(Categories[Category])), ROW(Categories[Category])^0)=0) *
  (Transactions[Date] >= AA11) *
  (Transactions[Date] <= AA12) *
  (Transactions[Category] <> "")
,""), LAMBDA(a,v, IF(SUM(--EXACT(a, v)), a, VSTACK(a, v)))))

Disable Wrap Text for cells L33 and below, if not already set that way.

Details

The new formulas leverage/use the Spending Trends sheet helper columns and were tested using Excel for the web.

Cell	Value
AA11	Start Date
AA12	End Date

Usage

The Spending Trends sheet can now be checked to monitor for Invalid Transaction Categories. The results are filtered by the Spending Trends selected time period.

For example, here is what three Invalid entries might look like now, where there are 3 total Invalid entries with unique values of Air Travel and Hotel (one of them occurs twice).
Cells G8, H8:
Spending Trends sheet

Cells L32 and below:
Spending Trends sheet

Now that you know how many Invalid Transaction Categories there are and what their names are, use the Transactions sheet Data Filter for the Transactions sheet Category column to select/correct the problematic entries. Transactions sheet Category needs to match a Categories sheet Category.

Permissions

Feel free to copy, use, and modify in your workflow.

Notes

The Spending Trends sheet already has a “Needs Categorizing” count for the number of categories that are blank within the Transactions sheet, so it makes sense to add a separate count for Invalid categories next to it, since these need proper categorizing as well.

The new formula results are filtered by the Spending Trends sheet Start/End Date, consistent with the original “Needs Categorizing” blank cell count.

Google Sheets is my primary Tiller use case and I used Excel for the web to test the Excel formulas with sample data.

FAQ

How did these Invalid entries happen?

They typically happen due to manual editing:

manually importing data
inserting rows into the Categories sheet
changing Categories sheet Category names without making the same change to the Transactions sheet, i.e. not using the Rename Category solution.
copy/pasting values into the Transactions sheet Category column that do not match categories in the Categories sheet. See the Troubleshooting the Category dropdown in the Transactions sheet help article and also read the How to Avoid this Issue section.

How do I fix them?

Here are some options, depending on how they occurred:

Change the Transactions sheet Category to one that matches a Categories sheet Category exactly, including number of spaces.
Add the Transactions sheet Category to the Categories sheet.
Fix the Transactions sheet Category dropdown list (data validation) and read how to avoid this issue.

But I do see the category on my Categories sheet, why is it flagged invalid?

Here are some reasons:

The new Spending Trends formulas are filtered by date, so check the selected time period includes the invalid category transaction date.
Check that the category names exactly match, including number of spaces.
The Transactions sheet Category column data validation might be incorrect - troubleshoot the Category dropdown

I see an invalid category on my Transactions sheet, why does the reporting say zero?

The new Spending Trends formulas are filtered by date, so check the selected time period includes the invalid category transaction date.

How do I check my entire list of transactions for invalid categories?

Change the Spending Trends time period (cell C6) to Custom and then change the Start Date, End Date to include your entire transactions date range.

What if I do not want to use/modify the Spending Trends sheet?

Consider using your own new/existing sheet and enter these two formulas into two different cells:

Google Sheets

Invalid Transaction Category count

=COUNTA(IFNA(FILTER(INDIRECT("Transactions!$D$2:$D"),ARRAYFORMULA(NOT(REGEXMATCH(INDIRECT("Transactions!$D$2:$D"),"^"&TEXTJOIN("$|^",TRUE,INDIRECT("Categories!$A$2:$A"))&"$"))),INDIRECT("Transactions!$D$2:$D")<>"")))

Invalid Transaction Category unique list (leave cells open below this cell, as it fills downward)

=SORT(UNIQUE(IFNA(FILTER(INDIRECT("Transactions!$D$2:$D"),ARRAYFORMULA(NOT(REGEXMATCH(INDIRECT("Transactions!$D$2:$D"),"^"&TEXTJOIN("$|^",TRUE,INDIRECT("Categories!$A$2:$A"))&"$"))),INDIRECT("Transactions!$D$2:$D")<>""))))

Where Transactions D and Categories A are the Category columns of each sheet, respectively. Change these column letters in the formulas to match your spreadsheet, if they are different. Note the date filtering is removed, so these check all transactions.

Microsoft Excel

Invalid Transaction Category count

=SUMPRODUCT(
  --(MMULT(--EXACT(Transactions[Category], TRANSPOSE(Categories[Category])), ROW(Categories[Category])^0)=0) *
  (Transactions[Category] <> "")
)

Invalid Transaction Category unique list (leave cells open below this cell, as it fills downward)

=SORT(REDUCE(,
FILTER(Transactions[Category],
  --(MMULT(--EXACT(Transactions[Category], TRANSPOSE(Categories[Category])), ROW(Categories[Category])^0)=0) *
  (Transactions[Category] <> "")
,""), LAMBDA(a,v, IF(SUM(--EXACT(a, v)), a, VSTACK(a, v)))))

Note the date filtering is removed, so these check all transactions.

Why do these formulas report invalid categories, but the Transactions sheet does not have the red triangle Invalid error?

It’s most likely a case-sensitive mismatch. The Transactions sheet Category data validation is case-insensitive.

Is there another way to find them?

Visually inspect the Category Tracker “Type / Group” column B for empty/blank cells - that Category is likely invalid as there is not a Category Type and Group defined for it - probably because this Category does not exist in the Categories sheet. The Category Tracker is a popular solution, but it is not included by default with a new Tiller Foundation Template.

heather · December 13, 2023, 10:16pm

This is so cool @Mark.S !! Thanks so much for publishing this solution. I get this question often

Mark.S · December 13, 2023, 10:26pm

Thanks, I hope it helps, and I certainly linked/referenced quite a bit of your help topics for support documentation

randy · January 8, 2024, 8:39pm

This is great and well documented solution to a common data integrity problem, @Mark.S. Thanks for sharing this with such great instructions and for both platforms. I just upvoted your solution.

abhiroopb · January 9, 2024, 1:31am

Not sure what I’ve done incorrectly, but Cell H8 is showing as “0” even though I can see I have invalid categories on my Transaction sheet.

Mark.S · January 9, 2024, 1:46am

Is the invalid category within the Spending Trends date range?

FAQ > I see an invalid category on my Transactions sheet, why does the reporting say zero?

abhiroopb · January 9, 2024, 1:58am

Thank you! Appreciate your quick response, that did the trick. I love this addition.

I made a small addition to this. If the cell is “0” then I use conditional formatting to make the text white. That way I’m not looking at a floating “0” indefinitely.

Mark.S · January 9, 2024, 2:04am

Incognito

Mark.S · March 13, 2024, 5:49pm

I updated the formulas to be case-sensitive. The Transactions sheet Category data validation is case-insensitive, so a category that is only different by case will not be flagged as an Invalid error, but case-sensitive mismatches causes issues in solutions/reports.

Topic		Replies	Views
Invalid Input. Must fall within a specified range Google Sheets	1	956	March 19, 2021
Category error: Invalid: Input must fall within specified range Ask Anything	4	21802	September 29, 2023
'Transactions' tab 'Group' category Error issue Google Sheets troubleshooting	3	138	January 12, 2024
Red triangle with invalid message on categories Ask Anything how-to , tcs-categorytracker	3	569	July 21, 2020
Input must fall within specified range error Ask Anything	3	3168	November 10, 2021

Finding Invalid Categories on Transactions Sheet

Overview

Installation

Setup

Usage

Permissions

Notes

FAQ

How did these Invalid entries happen?

How do I fix them?

But I do see the category on my Categories sheet, why is it flagged invalid?

I see an invalid category on my Transactions sheet, why does the reporting say zero?

How do I check my entire list of transactions for invalid categories?

What if I do not want to use/modify the Spending Trends sheet?

Why do these formulas report invalid categories, but the Transactions sheet does not have the red triangle Invalid error?

Is there another way to find them?

Related Topics