Monthly analysis issue in cell B/C:8

Google Sheets
1

When I have filter set to All, I get this error.

Screenshot 2025-04-05 at 9.27.10 AM
Screenshot 2025-04-05 at 9.27.10 AM598×291 22.5 KB

2

That could be ignored in this case, as it doesn’t affect the result, but it is nice to have a cleaner looking result without worrying that something is wrong :slight_smile:

It’s a data validation error, because cell B8 contains text (albeit text hidden by conditional formatting) and the data validation range is empty for when B7 = All.

So, you could delete the text in cell B8 to get rid of the error message,
OR
Update cell Z1 with a formula like this (you may need to unhide columns to see it):

={"C8 Dropdown";
LET(
  Groups, UNIQUE(FILTER(AJ7:AJ,AI7:AI=B6)),
  Categories, UNIQUE(FILTER(AK7:AK,AI7:AI=B6)),
  installText, "Business Expenses",
  IF(B7="Group",Groups,IF(B7="Category",Categories,VSTACK(Groups,Categories,installText)))
)
}

which will allow the data validation to match without error, even when there’s left-over Group or Category text in cell B8.

3

BTW, I do see that the Monthly Analysis template default install has Business Expenses in cell B8. Maybe that’s from testing, but that cell should probably be empty for the initial template install.

4

I’m pretty sure that data validation error is not an issue. There is an extra dropdown that can appear there depending on your selections and based on the choices in the green cells you have it is hiding itself. (Google doesn’t make it possible for the template to selectively turn off data validation.) This won’t affect the data displayed by the sheet.

5

True, but the data validation range list could be updated in the template for the All case, as I’ve suggested, to avoid getting the error.

6

I was so hasty this AM that I didn’t read through the thread before responding. Great point, @Mark.S. Sorry.