Urgent - Don't know how to do Conditional formatting rules

[genevievebr0]

Hi,
I want to do a variety of conditional formating but I don't know how to do so.
I have a sheet filled with data and I want to have certain informations flagged so I don't have to continously filter the columns to obtain the data.

Here is the document where I need to apply conditional formatting:


ConditionalFormatting_Examples.xlsx

I have put an example of each conditional formatting that I need to apply.

Can somebody please tell me the conditional formatting formula I need to use for each of these.

Also, it could be possible that two conditional formatting rules for the same cell can apply to what has been input. Can two conditional formatting rules be represented in the same cell?

Thank you very much for the help. I am going nuts trying to figure this out.
geneviève

[excelledsoftware]

conditional formatting is on the formatting ribbon. A quick Youtube search will pull up some examples. The other thing you will want to know is how to write an if statement. They are fairly simple.

=if(a1=2,True,False)

if the value of cell a1 is equal to 2 then this statement would be true and then your conditional formatting would apply.

[genevievebr0]

Hello,
Thank you for the help. However, I'm not sure this is possible with my report.

1. For the cells where I need to apply conditional formating, their is many possibilities of numbers that could be input. For example : Cell O12 and Q12 is identical. But also need rule to say if O12 and S12 or Q12 and S12 are identical. (see my excel report, situation is for the same code twice issue and same code & hours twice issue)
   • What would be the rule this?
   • I also need a rule when the same hours and same code is used twice. E.g. : N13-O13 would be the same as P13 and Q13. Again, I have 9 combinations possible. What would be the rule for this?
2. If there is a number (could be any number) in N7 and no number in O7. I have to put the same rule with P4 and Q4, R4 and S4, etc (total of 9 combinations). (see my excel report, situation for the hours with no code issue)
   • What would be the rule ?
3. If I want to highlight if P11 or R11 is higher than N11.
   • What would be the rule?
4. Hours are input in the cells N12, P12, R12, V12, X12, Z12, AC12, AE12 and AG12. I want to flag the numbers that are not in 15 minutes increments. Hours can vary between 0,01 hours to 48 hours. So everything outside 0,25 increments needs to be flagged. There are so many possibilities. (see my excel report, situation for the hours conversion not in 15 minute increments)
   • Is there a rule that can be applied?

I appreciate any person that will help me
Thank you!!

[gebobs]

Is there a reason why the values are text?

The first four are pretty straightforward setting formats for a single cell if it happens to equal a certain value. Do you know how to do that? The trick here will be to make sure it is looking for the text (e.g. "99") rather than the number.

The next two are a bit trickier, but can be done. I have done it on your sheet and will post it a bit later if you want. For these, you need to use a formula. For example, when you are checking if the os a Code 3, but no Code 2 or no Code 1, it would be something like:

=AND(isblank(S9)=FALSE,OR(ISBLANK(Q9),ISBLANK(O9)) )

The first part would be a bit simpler, S9>0, if the values were numbers instead of text. Another way to get around the text would be VALUE(S9)>0. Lots of ways to skin that cat.

I've only briefly looked at the remaining, but can't see any reason why they can't be done as well. I am heading into a meeting soon but can post it later if you like.

[gebobs]

Here's what I came up with. I did convert all those text "numbers" to numbers so if they must remain text, you will have to modify the CF formulas accordingly. That will add quite a bit of complexity to them so I recommend rather that you convert.

To give you an idea just how complex they are already, this is the one that checks if the same code and hours exist twice...

=AND(ISBLANK($N$13)+ISBLANK($P$13)+ISBLANK($R$13)< 2,OR(AND($N$13=$P$13,$O$13=$Q$13),AND($N$13=$R$1,$ O$13=$S$13),AND($R$13=$P$13,$S$13=$Q$13)))

First, it checks to make sure there are fewer than 2 blank code/hours. Thus if there are two blanks, it will not highlight them.

Next it compares 1/2, 1/3, and 2/3 and if any of them are have both code and hours the same, it will highlight.

[genevievebr0]

Hi Gebobs,

Thank you very much for your help. It is really appreciated.

I will have to ask if it is possible to convert the text "numbers" to numbers.
I had a couple of other questions,

1) I need to apply the same rule to many rows (example, row 4 to 500), how would the formulas look like?

2) Also, it could be possible that two rules apply for the same cell. How does Excel manage those? Does it take only the first rule? Would it put the cell in two colors if I put different colors for each formula?

Thanks again!!

[gebobs]

Originally Posted by genevievebr0

I will have to ask if it is possible to convert the text "numbers" to numbers.

If they can't be, it's quite easy to do within Excel.

1) I need to apply the same rule to many rows (example, row 4 to 500), how would the formulas look like?

I suspected as much. I see you use Excel 2010. In earlier versions, the limit on the number of rules for any given range was 3. As far as I know, there is no such limit now.

Would the format of your sheet be essentially the same as what you posted here without the Issue column? I could combine the rules and apply them over all the rows if you like.

2) Also, it could be possible that two rules apply for the same cell. How does Excel manage those? Does it take only the first rule? Would it put the cell in two colors if I put different colors for each formula?

As I mentioned above, there is no limit to the number of rules you can have. However, if two rules conflict, the topmost rule takes precedence. Thus you may have a problem if you have more than one rule that should process i.e. if you have an hour conversion and repeated codes.

However, if you are correcting these as you see the colors, you could just work your way through them until all the formatting is gone.

[genevievebr0]

Hi,

1) I need to apply the same rule to many rows (example, row 4 to 500), how would the formulas look like?

I suspected as much. I see you use Excel 2010. In earlier versions, the limit on the number of rules for any given range was 3. As far as I know, there is no such limit now.

Would the format of your sheet be essentially the same as what you posted here without the Issue column? I could combine the rules and apply them over all the rows if you like.

A- It would be nice if you could apply the rules to all of the rows. If there is no limit to the number of rows, could you put the rules from row 4 to 1000?

B- I unhidded all the columns so you can see the exact document that I will be using.

2) Also, it could be possible that two rules apply for the same cell. How does Excel manage those? Does it take only the first rule? Would it put the cell in two colors if I put different colors for each formula?

As I mentioned above, there is no limit to the number of rules you can have. However, if two rules conflict, the topmost rule takes precedence. Thus you may have a problem if you have more than one rule that should process i.e. if you have an hour conversion and repeated codes.

However, if you are correcting these as you see the colors, you could just work your way through them until all the formatting is gone.

A) I am not the one who will be correcting the issues. My job will be identify the issues and send them to the Team leaders who will need to verify the data and correct if necessary on a SharePoint file. So it won't be possible for me to see the different rules.
Besides having one rule with the "fill" in a color, another rule with the font in a different color, would you have any other suggestions?

Thanks again!

[genevievebr0]

oups, forgot to send you the new document attachement

[gebobs]

Originally Posted by genevievebr0

A- It would be nice if you could apply the rules to all of the rows. If there is no limit to the number of rows, could you put the rules from row 4 to 1000?

Yup.

A) I am not the one who will be correcting the issues. My job will be identify the issues and send them to the Team leaders who will need to verify the data and correct if necessary on a SharePoint file. So it won't be possible for me to see the different rules.
Besides having one rule with the "fill" in a color, another rule with the font in a different color, would you have any other suggestions?

Will the team leaders be using the Excel file? If so, then they can work through the conditions.

With so many different rules, I think it would be difficult, if not impossible, to distinguish them. There are only 4 formattable cell characteristics: number, font, borders, and shading.

[genevievebr0]

No actually, the way it will work is :
The report I sent you will be the raw data file. For every issue identified, I will copy the line in another document and indicate the type of issue in column C. So if 2 rules apply for the same line, I will copy the line twice so I can right each issue. The team leaders will not be using the raw data file.

[gebobs]

How about having another column with error codes? Or even another column for each error that would be highlighted if the error exists. That way, multiple errors would be readily apparent.

[genevievebr0]

Hmmm, not sure it would be possible. The table is already very wide. Is it possible to click on a conditional format rule and ask them to identify the cells? If so, I could do this for a couple of rules.

Another idea. Number all the issues. For all conditional rules found in a row, have the number of the issue written at the end of the report. For example, rule 3, 7 and 12 apply. These numbers would be entered in column AM, AN and AO. What do you think?

[gebobs]

Originally Posted by genevievebr0

Hmmm, not sure it would be possible. The table is already very wide.

The columns don't need to be wide if we make one for each code. Just wide enough to fit a single character.

Alternatively, we could make it one column and have an equation enter a code for each error encountered. Thus the cell error codes might look like "999L01 99P02 CNH CONV" or "1 3 6 12" or however you might like to code your errors.

Also, we could make an entirely new tab for the errors.

Another idea. Number all the issues. For all conditional rules found in a row, have the number of the issue written at the end of the report. For example, rule 3, 7 and 12 apply. These numbers would be entered in column AM, AN and AO. What do you think?

Precisely how I described one solution above. Great minds think alike. Fortunately, so do we. ;-)

[genevievebr0]

Hihi, I completely agree with you. Our minds work very well together

Since this is the Raw data file and not the actual document that has all my visual basic codes, we could add a few columns before column C so the numbers for the applying rules be shown. I prefer to see the numbers like 1 to 12 instead of the text.

Here's the numbers that I suggest for each issue :
1) 999 Code 01
2) 999 Code 02
3) 99 Code 01
4) 99 Code 02
5) Code 02, no Code 01
6) Code 03, no Code 01 or 02
7) Hours with no code
8) Code with no hours
9) Same code twice
10) Same code & hours twice
11) Most hours not first
12) Hour conversion

Would that be a good idea?