Page 1 of 1

### ColorScale formatting based on cells not being formatted

Posted: Fri Nov 29, 2019 3:46 pm
Copying Complex Formatting set on range B2-B20 for the cells in A2-A20 is easy. because you can set the formula to just operate on the cells in B2-B20, but active the formatting on A2-A20.

However ......

I'm not sure how (or if), you can do this for ColorScale formatting.

Reason ....

I want to output a fraction. But as an absolute fraction - so say 14/20 or 18/23 (To represent the amount of checks something has passed). Then alongside it I can write the value as a % by just doing =18/23

### Re: ColorScale formatting based on cells not being formatted

Posted: Fri Nov 29, 2019 8:07 pm
Open Office 6.0.7.3 on Ubuntu 18.04

I suppose it, that is LibreOffice...

### Re: ColorScale formatting based on cells not being formatted

Posted: Fri Nov 29, 2019 8:09 pm

### Re: ColorScale formatting based on cells not being formatted

Posted: Fri Nov 29, 2019 11:02 pm
LukeH7789 wrote:… because you can set the formula to just operate on the cells in B2-B20, but active the formatting on A2-A20.
LukeH7789 wrote:I want to output a fraction. But as an absolute fraction - so say 14/20 or 18/23 (To represent the amount of checks something has passed).
My guess is that you are asking about Format → Conditional Formatting. You cannot create 14/20 with any formatting. The only "fractional" formatting is Numbers → Category → Fraction, but this always reduces fractions to lowest terms so the ratio 14/20 will be displayed as 7/10. You can perhaps create "14/20" in another cell with a text formula, depending on your data which you haven't shown us.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

### Re: ColorScale formatting based on cells not being formatted

Posted: Mon Dec 02, 2019 10:49 am
So I think you've missed the point I was trying to make. I want to do the colorscale formatting on group of cells "A", based on the values in group of cells "B".

This can be done with *any other* formatting.

### Re: ColorScale formatting based on cells not being formatted

Posted: Mon Dec 02, 2019 1:48 pm
Conditional formatting in LibreOffice is weird. I think its broken. I can not find any way to apply a color scale (a feature that does not exist in OpenOffice) to a range based on values in another range. You have to create a couple of styles and apply normal conditional formatting based on formulas and styles. In LibreOffice relative references in conditional formattings refer to the top-left cell of the range to be formatted.

### Re: ColorScale formatting based on cells not being formatted

Posted: Mon Dec 02, 2019 6:22 pm
Maybe a workaround:
• first put in Column A by formula
Code: Select all   Expand viewCollapse view
=B1
the same values as in Column B.
• then modify the properties of the cells in column A on Tab "Cell Protection" to "Hide all"