Combining conditional formatting and manual values in the same cells

English
,
#1

Hi,

It is my first pose, so I apologize in advance for a lack of redacting skills. My goal is to make an automatic Gantt diagram that has for each cell representing a date 3 states (time frame not assigned for this task, time frame assigned but task not done, time frame assigned and task done). To do this I’d like to use conditional formatting but also manually write in the same cells to tell if a task is done or not. The question is whether this is even possible to do, and if that’s the case, how do I insert a condition in a boolean operator like AND on its self value ?

To give a non-example, here’s this : https://www.youtube.com/watch?v=1y40xTIEKbs&t=764s
Basically not using an external factor other than the time cells themselves for the calculations.

Thank you.

#2

Maybe it will help you:

1 Like
#3

I agree with @Zizi64 to not use Calc for this, or at least take advantage of others’ work.

But, for the record, if you just want the cells to show text as well as color, you can do the Conditional Format so that you have a quoted string under the Number format for the style applied by the CF.

  1. Create three styles, NotAssigned, OnGoing, Complete, and set them up with, say, red, yellow, and green backgrounds, meanwhile under the Number tab in the Format Code line put “To assign”, “In prog…”, “Done!”, respectively and in quotes. This isn’t “manual”, but it seems to replicate that state information the way you are asking, as text and not just color.

  2. Now, according to whatever criterion you have for each cell, assign the respective styles to each CF entry. For example, CF entry Formula Is…G4=1 if G4 is the top left (or only) cell you are CF formatting and style NotAssigned. Then if you put a 1 in G4 it will turn red and show the text “To assign”.

#4

Maybe you missed the possibility to set conditional formatting to “formula is”. Then you can use the same functions as in cells, so you can use not and and-operator, but the function AND like AND(A1=0; C1=50) to combine several conditions.

#5

Yes it worked, thank you. I mostly wasn’t sure about the syntax and what types were allowed for boolean operators. The question that could have hindered my work is whether conditional formatting would influence the written value of a cell, which it doesn’t. After those locks were gone, I got it solved pretty fast thanks to your examples.

Handing out the sample I was testing, you can find the rules for more clarity if needed.

Modele_Gantt.xlsx (8.7 KB)

PS : is there a way to program User Defined Functions in Calc ? Could be useful to know in case it’s needed.

#6

User defined functions

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.