The workaround that user NegativeActivity3184 found, placing sheets between two bookend worksheets and referencing them as `First:Last!Cell`, is clever, but it exposes a deeper limitation in how traditional spreadsheets handle structure. You can make the sum dynamic, but you cannot make it conditional on a per-sheet flag without breaking the elegant simplicity of that range reference. That is the kind of friction that should not exist in a modern data tool.
What this user is really asking for is a way to treat each worksheet as a modular unit that can opt into or out of a calculation. The `First:Last` trick works because it treats the sheet order as a fixed, sequential range. But adding a conditional toggle in each sheet, say, a cell that says "include" or "exclude", forces you to abandon that range-based approach. You end up writing a formula that must check every sheet individually, which defeats the purpose of having a dynamic range in the first place. The user has already done the hard work of identifying the problem: they want the formula to be aware of metadata within each sheet, not just the sheet's position.
This is where an AI-native spreadsheet can step in. Instead of forcing the user to choose between a clean dynamic range and conditional logic, an intelligent system could let you define a rule like "sum cell A1 across all sheets where sheet-level flag B2 is set to TRUE." The formula becomes a single, readable instruction that respects both structure and content. The user does not have to restructure their workbook or maintain a separate index sheet. The tool handles the iteration and filtering behind the scenes.
Our take is straightforward: the workaround works, but it should not be necessary. Spreadsheet users should not have to choose between automation and conditional logic. When a tool can understand both the layout of your workbook and the decisions you embed inside each sheet, you stop fighting the tool and start focusing on the question you actually wanted to answer. That is the shift worth exploring.