This is a classic case where the tool's logic is sound, but its behavior feels like a trap. The user added a formula column to a table, tried to FILTER on that column, and got an #N/A error. That's frustrating, and it's easy to blame the spreadsheet. But the real issue isn't the FILTER function, it's the mismatch between what the formula column contains and what FILTER expects to read.
Let's be clear about what's happening. A FILTER function evaluates an array of TRUE/FALSE values from a condition. When you write a condition like `E2:E100 = "Yes"`, it works fine if column E holds static data. But when column E is a formula, say, a concatenation or a logical test, the spreadsheet may not evaluate that formula in time for FILTER to read the results. The result is an #N/A, not because the data is missing, but because the calculation order is fighting against you. The spreadsheet sees a blank or an unresolved reference where it expected a value.
For anyone building shadow tables or dynamic reports, this is a practical limit of traditional spreadsheet architecture. The fix isn't complicated, wrap the formula column in an IFERROR or ensure the formula returns a value for every row, even if that value is empty text. A better long-term approach is to use LET or LAMBDA to create a single formula that handles the entire filter without relying on a helper column. That keeps the logic self-contained and avoids the timing issue entirely.
What this story really reveals is how quickly a simple task can expose the brittle edges of spreadsheet design. Users shouldn't have to guess whether a column is "ready" for FILTER to read it. The tool should handle that. That's why we believe the future of data work isn't about patching workarounds, it's about building tools where the calculation engine and the data model are unified from the start. Until then, the fix is straightforward: make sure your formula column returns a value for every row, and if that doesn't work, move the logic into a single formula that doesn't depend on a helper column at all.