The error message is associated with the Category column. I have 50 categories and at least one transaction has been categorized with a invalid designation. I cannot find the offending cell(s). Any know of a trick to uncover the error on my part.
Thank you Mark, I was unaware of the option but when experimented with revealed null results.
1 Like
Welcome @gregrich57 
For the cells where you’re seeing that error in the Category column you’ll want to clean up the entries by reselecting them manually or fixing the category.
The reasons you’d see this:
- The category does not exist on the Categories sheet
- The category is outside the data validation rule’s range (doesn’t include all the rows on the Categories sheet)
- The category is misspelled/spelled differently
- The category has a trailing or leading space (space at the front or end of the category)
- Google is being weird - I’ve seen this from time to time
The best place to start is to follow the linked guide to sort of “reset” the data validation rule and then if that doesn’t fix it, just reselect your categories. If things look exactly the same between the dropdown and what was already there and it’s complaining about it being invalid, it’s probably a leading or trailing space.
AutoCat can sometimes be the culprit, if the Category column on that sheet isn’t also data validated, it will write invalid entries to the Category column with misspellings or blank spaces.
Does that help?
1 Like
I just wanted to check that you had the entire sheet selected before enabling the data filter?
I typically will just select the remove filter icon while one cell is selected anywhere in the sheet and then select the box to the left of column A and above row one to select the entire sheet. After the entire sheet is selected, select the same filter icon to enable data on all columns. Then try the Data not validated filter on the Category column.
1 Like