FILTER leaving behind ghost values in Google Sheets

1
votes

I have some FILTER functions lined up in the grey cells shown below.

The problem is that when data changes, oftentimes Sheets will leave behind ghost values that need to be manually deleted. There's no way I can keep doing that. It's totally breaking the functionality of my spreadsheet.

The only valid FILTER result is in Column M shown below. You can tell because there's actually a value in the grey cell.

Data With Ghost Errors

How can I get around this glitch? Is there a way to force these formulas to refresh or something? I tried SpreadsheetApp.flush(); and that didn't do anything.

2
google-sheetsgoogle-sheets-formula
Can you share a copy of your sheet? It sounds like something strange is going on because formula can't leave behind values if they are deleted. You mention SpreadsheetApp.flush() so maybe you have some Apps Script going on behind the scenes, are you able to share that? - iansedano
This is a known bug and only happens when you have two filters immediately next to one another. - MattKing
@MattKing do you have a link to the issue? - iansedano
I'm working on the escalation at the Google Product Forums. Will post back here when I complete it - MattKing
@iansedano The issue isn't that the formulas are leaving behind values when they are deleted. The issue is that the actual results for the FILTER change, but the changes are not being reflected in the actual output onscreen. So, say for example, it was filtering 10 results at one point. Then, the criteria changed and now there are zero results. What happens then is that the old results just "stick" there and have to be manually deleted. The only value that actually disappears like it should is the one with the formula itself -- all the other results below remain. - Aaron

2 Answers

0
votes

In the meantime You should never need to have multiple FILTER() formulas next to one another that are filtering based on the same thing.

write just ONE filter formula with your desired ranges placed in curly brackets with commas between like this:

=FILTER({Data!A2:A,Data!G2:G,Data!X2:X}, Data!B2:B>= A230)
0
votes

I have filed an issue on the Issue Tracker to track this.

If you are affected by it go and "star" it to let Google know!

https://issuetracker.google.com/issues/190535938