This user's problem is a textbook example of how fragile traditional spreadsheets can be when they're asked to do something as simple as survive deletion. The formula `=IF(COUNTA('Sheet 4'!B35:B43,'Sheet 5'!B35:B43),"Y","N")` is logical on paper, but it breaks in practice because `COUNTA` treats a missing sheet reference as an error, and in many spreadsheet engines, an error in a range reference doesn't always collapse the formula cleanly. When either sheet is deleted, the function can't evaluate the range, so it returns something unexpected, often a zero or a blank that `COUNTA` interprets as a value. That's why the formula stubbornly returns "Y" even when both sheets are gone.
What this means for you is that your template's design is fighting against the tool's default behavior. You're trying to keep the document clean by removing unused sheets, but the formula doesn't know how to handle a missing reference gracefully. The practical fix is to wrap each sheet's range in an `IFERROR` check, so that if the sheet is deleted, the formula treats that part of the range as empty rather than as an error. For example, something like `=IF(IFERROR(COUNTA('Sheet 4'!B35:B43),0)+IFERROR(COUNTA('Sheet 5'!B35:B43),0)>0,"Y","N")` will handle the deletion cleanly. It's not elegant, but it works.
This is the kind of friction that makes users feel like they're fighting their tools instead of being empowered by them. You shouldn't have to patch a formula just to delete a sheet without breaking your data validation. A smarter approach would be to use a named range or a helper cell that centralizes the check, so that deleting a sheet doesn't rip the formula's legs out from under it. But the real lesson here is that spreadsheet formulas were never designed to be modular or resilient to structural changes. They assume a fixed world. When you try to make that world dynamic, you hit limits that feel arbitrary.
So don't accept that this is just how spreadsheets work. It's how they've always worked, but that doesn't mean it's how they should work. You're right to want a formula that survives your edits. The solution exists, it just requires a workaround instead of a feature. That's the gap we're here to close: making your data logic as adaptable as your workflow.