Condition formula

[Pascale]

SI(G4="";SI(G4="no";0;"";NB.JOURS.OUVRES(G4;AUJOURDHUI())/5))
I get a error message with this formula, it tels me that I have to
arguments in my formula, How can I fix this?

 

[sebastienm]

Hi,
You need to close the parenthesis after the second "" (and remove the last
parenthesis)
SI( G4=""
;SI( G4="no"
;0
;""
)
;NB.JOURS.OUVRES(G4;AUJOURDHUI())/5
)
You can write a formula on multiple lines to make it more readable; while
editing the formula, just press ALT+ENTER or (ALT+RETURN) to add a line.

 

[Pascale]

Hi,

That was very helpfull, thank you.

I now would like to know if I can do this:
In this same file, the column with the LOA has the condition to print blue
when it is written LOA inside of it, Now can I have the name, that is have
the first column print out in the same blue, and if yes, How can I do that?
Archambault, Ginette PM FR 9 LOA KE IBM


Thank-you so much.

 

[sebastienm]

Say the names are in column A and the 'LOA's in column E, and data starts in
row 2.
We want to apply the conditional format on these 2 column when the condition
=($Ex="LOA") ''' where x is the row number
is True

- Select E2
- menu Format > Conditional Formatting, set:
- instead of 'Cell Value Is', use 'Formula Is'
- then enter: =($E2="LOA")
- choose your format and click OK
---> right now , only E2 has the Conditional FOrmat
- Now, select the entire column E and entire column A
- go to menu Format > Conditional Formatting:
- the conditional format we set up earlier should be in there by default
- click OK

This could also have been done in 1 single shot by selecting directly A:A
and E:E but by going by steps we avoided a few potential issues.

Now, in you want to convert to VBA, run the Marco Recorder (Enregistrer une
macro) and re-do the above process, then just adapt the code.

 

[Pascale]

Hello,

The invite was very helpfull, It worked, now I would like to do one more
thing to this file, I would like to have the same thing happen, with another
color, but instead of being LOA it is numbers that appear and the color for
that column is orange. In other words, when Column E has LOA it pops out blue
and so those the Name (A)and when there is a number higher then five in the
column H, that case pops out Orange, I want that same condition to appear on
the names in column A.


Thank you for your help.

 

[sebastienm]

The first condition currently works for LOA in col E and set A in blue
Now we add a second condition to have A in Orenage when H is greater than 5.
- Select A2
- menu Format > COnditional FOrmatting.
- you can see the first (Blue) condition by default.
- now click the Add button to add the new condition and set it to :
- Formula Is
- enter: =($H2>5)
- and select the Orange format
- click Ok
- Select column A
- menu Format > COnditional Fromatting
- both formats should show
- click OK

Note1: You can add up to 3 conditional formats per cell.
Note 2: If you have multiple conditional formats in a cell say 1,2 and 3,
the cell gets the format from the first condition that is satisfied. Eg in
your case, if E3 contains LOA and H3 contains 10. Then both conditions are
True, but since the first one is the 'blue' one then cell A3 will be Blue.
If you want it to be orange, you would have to declare the 'orange' conditon
format first (or at this point, delete the blue one and keep the orange, then
add the blue one again; it will be placed in second position)