Hi there,
My business uses LibreOffice quite extensively, especially Calc. Recently we have started using conditional formatting to make using the sheets more friendly for our less computer-savvy users. However, when we apply the conditional format and SAVE the file, we can reopen the file right afterwards and the conditional formatting is gone. Has anyone else had this problem?
Thank you in advance!
Do you use the native format to save?, What is your LibreOffice version and OS?
Cell Value Conditions “duplicated values” and “non duplicated values” not stored in the file. Did you mean these formats?
Yes, I the condition “duplicated values” is the condition we have started to use. Is there a way to achieve the highlighting of duplicate fields every time the file is opened? On any computer?
Try to use the old method of conditional formatting for this case: “formula is” COUNTIF (;) > 1
I have tried to use the method provided and it works somewhat. The only problem is that not all of the duplicates are highlighted and sometimes cells that do not have a duplicate are highlighted. I will try it again, maybe I was using the formula incorrectly before.
Usually I’m wrong when setting the second parameter of this formula. It is necessary to specify the address of the active cell (encircled black border). For example, it is necessary to mark duplicates in the range A2:C140. Select this range and see the address of the active cell… (to be continued)
(cont.) … active cell (it can be A2, C2, A140, C140, or any other, depending on the method of selection). Let this be C140. So the formula should be COUNTIF($A$2:$C$140;C140)>1 (Pay attention to $-signs)After that select any other cell in the tested range and check that the formula points to it
Thank you! It worked great. I was missing one $! Just 1! Thanks for your help!
Is there a way to make one of the duplicates, or all, show how many times that data has been duplicated? Like for phone numbers I might have 555-555-5555 duplicated 5 times. They all get highlighted.Could I make a formatting rule that will change the cell(s) to read 555-555-5555 +5
Yes, it is possible. See the second example in the answer
Conditions “duplicated values” and “non duplicated values” created for validate existing data only. This works only in the current session and not saved in a file. If you need to store these conditional formats in your workbook, use classical equivalents:
"duplicated values" =>
COUNTIF (<absolute address of range checked>;<relative address of the current cell>) > 1
"non duplicated values" =>
COUNTIF (<absolute address of range checked>;<relative address of the current cell>) = 1
You can also use the function STYLE(), either alone or in combination with conditional formatting.
Small example - Show how many times duplicated.ods
Example of alternative solution - Combination of conditional format and STYLE.ods
Hello!
I have same problem with conditional formating. I use it to automatic color the weekends in a month. It’s all working good until I copy the sheet and save the file. After I open the file again the sheet that is a copy, it doesn’t have conditional formating anymore. Is there a bug? The copy sheet have all the conditional formating until I reopen the file.
Regards,
F
A copy of the sheet contains the styles that have been created to format the weekends?
Yes it have. I did copy the whole sheet. It have also the formulas etc… but there is no formating effect. After I make the copy of the sheet I still have the conditional formating on both sheets. But after save/reopen the file, I have the cond. formating only on original sheet.
Any news about this bug? Has been long time since last post but still have same problems with ver 4+ 
Still no ideas about that bug? Here I did add a Calc where are two sheets. One of original default formating and the other copy sheet. Conditional formating.ods