This user's problem is a classic example of how traditional spreadsheets force you to choose between logic and flexibility. The formula in column O works perfectly until someone enters a date, then the autofill breaks, and every new row is orphaned. This isn't a user error; it's a design limitation. Spreadsheets were built for static grids, not dynamic workflows where data changes hands. When you have to override a formula with a manual entry, you are essentially telling the tool that your process is more complex than its architecture can handle.
What this means in practical terms is that you are now managing two separate systems: the formula logic and the manual data entry. Every time you paste a date over that formula, you are creating a gap in your automation. The spreadsheet can't tell the difference between "I intentionally entered this date" and "I broke the rule." It's a brittle setup that works only as long as nobody touches the cells. For teams, this becomes a recurring headache, someone forgets, a formula gets overwritten, and suddenly column O shows blanks where it should show "N/A." The solution isn't to train people to be more careful. The solution is to use a tool that separates the display logic from the data entry.
A smarter approach is to treat columns O and P as pure data entry fields, and move the conditional display to a helper column or a separate view. In an AI-native spreadsheet, you would write a rule that says "show 'N/A' when column N is 'Yes' and no date has been entered," rather than embedding that rule directly into the cell. This way, entering a date doesn't destroy the logic, it simply updates the condition. The autofill stays intact because the formula never lives in the cell you are editing. It lives in the layer above, watching and waiting.
The bottom line is this: if your workflow requires both automation and manual overrides, your spreadsheet needs to support both without conflict. The moment you have to choose between keeping your formulas working and entering real data, you have outgrown the tool. Look for a solution that lets you define rules separately from data entry, so your logic survives every edit.