Help with Conditional Formatting in range of cells

[ChuckW]

Libre Office 4.4.2.2, English, Calc
Win7 Ultimate, English

I am creating a “Work Status” spreadsheet to easily show the current position of any unit being worked on via a unique color. The sheet is arranged to show the stages of work completion in columns and the unit identification in rows. There are 10 stages of completion.

The cell in each “stage of completion” is either blank, or has a date. I have created “styles” with the corresponding background color for each stage. The styles are named by column ID (K,M etc). My intent is to adopt the color of the cell closest to completion in all the preceding cells on that row.
I have tried conditional formatting in cell J2 (preceding cells contain unit information) with:
“Formula is” IF(K2<>""
“Apply style” K
“Cell Range” B2:J2.

The problem I have is: IF the referenced cells are not blank AND have another conditional format style set, they do not change to the new one. I have tried both multiple conditions per cell and one condition per cell (keeping the Range of Cells constant for that cell) but still have the same problem.

Using “NOT NULL” might be the wrong formula so I have tried to find a list of “working” examples of functions but have so far failed.

Is there a way to insert a Style or Color from within a formula. I found Color in the function list but not a very good explanation of it’s use. I don’t know if the R G B colors are in hex or decimal. (Tried both, both failed, probably because of my ignorance Oh Well)

ANY assistance will be GREATLY appreciated.
Thanks in advance.
Chuck

Thanks for the suggestion to upload.
SHEET1 is what I have, SHEET2 is what I would like to have.

SHEET1.jpg
SHEET2.jpg

[Lupp]

Why not attach an example file demonstrating the problem and your expectations? Less words an less misunderstandings, more clarity in most cases. I will care for enough “Karma”.

[Lupp]

Sorry! My way in such cases is to design a solution along the “real thing” which is a spreadsheet document and not an image. Based on a picture this would require to create a document, fill in sample data, and then implement a solution. Sorry again! Better read the answer I already gave and implement the solution yourself.

[Lupp]

Cells will apply the first (not the last!) conditional format for which the condition came out TRUE.

Is there a way to insert a Style or Color from within a formula.

Read about the STYLE function. It can sometimes be used for elegant solutions using ‘Conditional Formatting’, too. Also have a look on CHOOSE.

Appended by editing:

Possibly the attached demonstration can help. Complicated conditional formatting tends, however, to influence the efficiency of sheets badly, and sometimes even to crash Calc.ask53695ComplicatedCF001.ods

[PYS]

Hi - Due to a bug inherited from OOo just be warned of the risk of blinking in print preview. The workaround is to disable the automatic recalculation.

Regards

[Lupp]

Thanks to @PYS ! Supposedly the same bug will often cause a rapid blinking of the cursor when editing inside the grid area.

[AlexKemp]

Yes, you CAN apply a style to a cell from within a formula. (Info on ‘=style()’ is at bottom & also within the Help information under ‘STYLE function’.) You will need to have created some named-styles, each with the required background colour. Then use =style("name-of-color-style") to apply that style to that cell.

Number formatting initially strikes me as the best way to handle this, with the unfortunate fact that COLOR (used within the format-code for number formatting) is restricted to 6 HTML named-shades.

"Is there a way to insert a Style or Color from within a formula?"

Info on COLOR():

The format is:-

=color( red, green, blue, alpha )

red, green blue: integer 0-255
alpha (opacity, optional): decimal min 0 (transparent), max 1

(to investigate further):

• place =DEC2HEX( COLOR( A1, A2, A3 )) in a cell
• place decimal numbers between 0 & 255 in A1, A2, A3
  
  

the cell will now show a HTML color number

Style() is also within the Help info:

Info on STYLE():

The format is:-

=style( “Style”; Time; “Style2” )

“Style” will assign this cell-style to the cell (needs to be pre-defined)
(the latter 2 are optional)

If this helps then please tick the answer ().

[ChuckW]

THANKS to all for your answers. I will use them to help solve my problem.

Sorry, I misinterpreted your suggestion to upload an example. This is my second post to the forum and I plead ignorance to what is obvious to others.
The instructions on the site recommended the question be accurately explained. Once again, a "Newbe’s mistake. I apologize for the excess verbiage.

Hopefully in time I’ll improve.

[Lupp]

And I hope you won’t blame me too badly for my rather rough tone. It isn’t easy for me to find the right words in English. Ignoring one of my own comments I prepared an example meanwhile.The task looked rather compicated. You may judge yourself if my suggestions meet your expectations. See amendment to my original answer.

[ChuckW]

THANK YOU for your example. In my opinion, you explained the operation and syntax of both style and color, including restrictions, in a way that I understood. I struggle with the correct syntax when combining functions and rely on what examples I find to guide me. Much trial and error, but that is how to learn.
Again, Thank you again for taking the time to help me. Best regards…