|
Condition formula
SI(G4="";SI(G4="no";0;"";NB.JOURS.OUVRES(G4;AUJOUR DHUI())/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? |
Condition formula
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. -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Pascale" wrote: SI(G4="";SI(G4="no";0;"";NB.JOURS.OUVRES(G4;AUJOUR DHUI())/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? |
Condition formula
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" wrote: 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. -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Pascale" wrote: SI(G4="";SI(G4="no";0;"";NB.JOURS.OUVRES(G4;AUJOUR DHUI())/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? |
Condition formula
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. -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Pascale" wrote: 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" wrote: 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. -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Pascale" wrote: SI(G4="";SI(G4="no";0;"";NB.JOURS.OUVRES(G4;AUJOUR DHUI())/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? |
Condition formula
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" wrote: 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. -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Pascale" wrote: 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" wrote: 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. -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Pascale" wrote: SI(G4="";SI(G4="no";0;"";NB.JOURS.OUVRES(G4;AUJOUR DHUI())/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? |
Condition formula
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: =($H25) - 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) -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Pascale" wrote: 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" wrote: 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. -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Pascale" wrote: 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" wrote: 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. -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Pascale" wrote: SI(G4="";SI(G4="no";0;"";NB.JOURS.OUVRES(G4;AUJOUR DHUI())/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? |
| All times are GMT +1. The time now is 12:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com