Page 1 of 1

ColorScale formatting based on cells not being formatted

PostPosted: Fri Nov 29, 2019 3:46 pm
by LukeH7789
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

PostPosted: Fri Nov 29, 2019 8:07 pm
by Zizi64
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

PostPosted: Fri Nov 29, 2019 8:09 pm
by Zizi64
Please upload your .ods type sample file here.

Re: ColorScale formatting based on cells not being formatted

PostPosted: Fri Nov 29, 2019 11:02 pm
by MrProgrammer
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

PostPosted: Mon Dec 02, 2019 10:49 am
by LukeH7789
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

PostPosted: Mon Dec 02, 2019 1:48 pm
by Villeroy
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

PostPosted: Mon Dec 02, 2019 6:22 pm
by F3K Total
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"
  • then menue Tools, Protect Sheet..., no password necessary
This will hide the doubled values in column A, looks like:
CF.PNG
CF.PNG (3.9 KiB) Viewed 421 times