Forum Discussion
Solved
filter stopped working
I have the following in my spreadsheet: =IFERROR(TEXTJOIN(",", TRUE, FILTER(daily!$AL$5:$AL$1001, (daily!$AJ$5:$AJ$1001=1) * (daily!$X$5:$X$1001=D86) * (daily!$AN$5:$AN$1001=D87))), "") where D86 has ivv and D87 has Communication. This has stopped working 2 days ago, and now returns nothing. If I remove (daily!$AN$5:$AN$1001=D87), it works. So there seems to be an issue with column AN. I have checked that values in column AN are text, and done other checks, but can't figure out why it's suddenly stopped working. I used Copilot to troubleshoot but even it couldn't find the issue, and kept repeating fixes which didn't work. Would appreciate some suggestions from "fresh eyes" looking at it. Other filters in the sheet work, only the ones that references column AN in the daily sheet
Hi Patrick, many thanks for that suggestion. There were several #N/As in the spill results, and after correcting these, it's all working again. Best regards.
5 Replies
The issue is likely with the values in column AN. Even if they look fine, they might have hidden characters, extra spaces, or formatting issues. Here are quick things to try:
- Check for hidden spaces: Use
=TRIM(CLEAN(daily!AN5))=TRIM(CLEAN(D87))
to see if the values truly match. - Check data type: Use
=ISTEXT(daily!AN5)
to confirm all cells are text. - Try using cleaned values in the formula:=IFERROR(TEXTJOIN(",", TRUE, FILTER(daily!$AL$5:$AL$1001, (daily!$AJ$5:$AJ$1001=1) * (daily!$X$5:$X$1001=D86) * (TRIM(CLEAN(daily!$AN$5:$AN$1001))=TRIM(CLEAN(D87))) )), "")
It’s probably a formatting or character mismatch in column AN. Hope this helps!
Hi Chris, thanks for the reply. I'd tried those suggestions before posting here, but they didn't help. The earlier reply from Patrick fixed things.
- Check for hidden spaces: Use
You may want to place this in a cell and let it spill:
=(daily!$AN$5:$AN$1001=D87)Next, check for results that are not TRUE/FALSE.
Hi Patrick, many thanks for that suggestion. There were several #N/As in the spill results, and after correcting these, it's all working again. Best regards.
You're welcome! Glad it's been fixed.