Conditional formatting should be the easy part. You define the rule, you set the range, and the sheet does the rest. So when a user like u/Own_Act_1087 writes a correct formula in a test column, sees it return TRUE for every date inside the financial year, and then copies that same formula into the conditional formatting rules manager only to watch it fail, the frustration is entirely justified. This isn't a user error. It's a gap in how most spreadsheet tools handle cross-sheet references inside formatting rules, and it's one that shouldn't exist.
The core issue is that conditional formatting in traditional spreadsheets treats cell references differently than regular formulas do. When you write `=AND(G3>Settings!$B$6, G3 For users managing dynamic date ranges tied to a settings sheet, this is a real productivity bottleneck. You end up maintaining a visible helper column you didn't want, or you hardcode the financial year dates into the formatting rule itself, which defeats the purpose of a central settings sheet. Neither option is acceptable for a workflow that should be automated. The solution, and the point we want to make, is that the tool should handle this natively. A conditional formatting rule that references a named range or a cell on another sheet should be treated with the same reliability as any other formula in the workbook. This is exactly the kind of friction that an AI-native spreadsheet can remove. Instead of forcing users to debug reference quirks, the system should understand intent: "highlight the entire row if the date in column G falls within the financial year defined on the Settings sheet." That's a single natural-language instruction, not a multi-step workaround. Until that becomes standard, users like u/Own_Act_1087 will keep spending time on workarounds that should be unnecessary. The technology exists to make this seamless. The question is when the tools will catch up.