I have a spreadsheet that uses VLOOKUP to read values from an external spreadsheet. There are two columns with color scale conditional formats (red, yellow, and green). After recalculating, the cell values update fine, but the conditional formats contain only red and green. Yellow is missing. If I close and reopen the spreadsheet the conditional formats are correct–yellow is there. Is this a bug? Is there a macro command I can call to have the conditional format refresh correctly without reopening the file?
Please edit your initial post and add the Calc version (menu Help > About LibreOffice).
What happens when you perform a hard calculation (Ctrl + Shift + F9)?
If you can add a file without sensitive data (7th icon on the message tool bar).
What are the rules for recalculation of conditional formatting?
My issue is this:
- Ensure AutoCalculate is on.
- Set a Named Expression to a value. Let’s say SpecialValue to 15.
- Add a conditional format on a range of cells using Formula Is SpecialValue=15.
- The range format reflects the conditional formatting correctly.
- Change the Named Expression to 14.
- The conditional formatting does not change, even after a Hard Calculate.
+++
What does trigger a true refresh of the conditional formatting: - A close-and-load cycle
- Perform the following:
-
- Select the entire conditionally formatted range as listed in the conditional format manager.
-
- Apply a local format change.
-
- Undo that with Ctrl+z. At that point, the conditional format refreshes.
Version: 25.2.4.3 (X86_64) / LibreOffice Community
Build ID: 33e196637044ead23f5c3226cde09b47731f7e27
CPU threads: 20; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded
The same behavior appears in Calc v7.
ConditionalFormatAndNamedValue.ods (10.6 KB)
I don’t think that request 160255 is anything like what my issue is. I’m not discussing the range THAT is conditionally formatted at all, other than that as an observation, if that complete range is selected, then Ctrl+z can trigger an update.
I’m discussing a simple value named expression that changes value after conditional formatting using that named expression has been setup. I cannot find any bug report closely related to what I’m asking here…but someone could prove me wrong; there’s lots on Bugzilla.
@EarnestAl, are you saying you could duplicate the issue?
Using your sheet I can duplicate it in 25.8.0.0 beta1, 7.6.7.2 and in 6.7.4.2
I did see tdf#91385 and wondered if it were related but that seems still to be fixed in 25.8
Thanks @EarnestAl . I may file a bug. @mariosv, looking back at my response to you it reads as being snippy. I was in diagnose mode. Sorry if I was impolite.