ColorScale formatting based on cells not being formatted

Discuss the spreadsheet application

ColorScale formatting based on cells not being formatted

Postby LukeH7789 » 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
Open Office 6.0.7.3 on Ubuntu 18.04
LukeH7789
 
Posts: 2
Joined: Fri Nov 29, 2019 3:31 pm

Re: ColorScale formatting based on cells not being formatted

Postby Zizi64 » Fri Nov 29, 2019 8:07 pm

Open Office 6.0.7.3 on Ubuntu 18.04

I suppose it, that is LibreOffice...
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9538
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: ColorScale formatting based on cells not being formatted

Postby Zizi64 » Fri Nov 29, 2019 8:09 pm

Please upload your .ods type sample file here.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9538
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: ColorScale formatting based on cells not being formatted

Postby MrProgrammer » 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
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3960
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: ColorScale formatting based on cells not being formatted

Postby LukeH7789 » 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.
Open Office 6.0.7.3 on Ubuntu 18.04
LukeH7789
 
Posts: 2
Joined: Fri Nov 29, 2019 3:31 pm

Re: ColorScale formatting based on cells not being formatted

Postby Villeroy » 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28540
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: ColorScale formatting based on cells not being formatted

Postby F3K Total » 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"
  • 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 416 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 974
Joined: Fri Dec 16, 2011 8:20 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests