![]() |
#1
|
|||
|
|||
![]()
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 |
#2
|
|||
|
|||
![]()
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.
Code:
=if(a1=2,True,False) |
#3
|
|||
|
|||
![]()
Hello,
Thank you for the help. However, I'm not sure this is possible with my report.
![]() Thank you!! |
#4
|
|||
|
|||
![]()
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. |
#5
|
|||
|
|||
![]()
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. |
#6
|
|||
|
|||
![]()
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!! ![]() ![]() |
#7
|
|||
|
|||
![]() Quote:
Quote:
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. Quote:
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. |
#8
|
|||
|
|||
![]()
Hi,
1) I need to apply the same rule to many rows (example, row 4 to 500), how would the formulas look like? Quote:
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? Quote:
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! ![]() |
#9
|
|||
|
|||
![]()
oups, forgot to send you the new document attachement
![]() |
#10
|
|||
|
|||
![]() Quote:
Quote:
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. |
#11
|
|||
|
|||
![]()
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. |
#12
|
|||
|
|||
![]()
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.
|
#13
|
|||
|
|||
![]()
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? |
#14
|
|||
|
|||
![]() Quote:
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. Quote:
|
#15
|
|||
|
|||
![]()
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? |
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
info_guy2 | Excel | 1 | 07-03-2014 10:07 AM |
Conditional Formatting | teza2k06 | Excel | 3 | 01-19-2014 02:34 AM |
Creating multiple conditional rules on one column | secoo140 | Excel | 0 | 10-07-2013 12:13 PM |
URGENT!!! Powerpoint Image Formatting and Positioning Macro | mertulufi | PowerPoint | 5 | 12-20-2011 10:14 AM |
![]() |
Lucky | Excel | 2 | 10-03-2011 11:41 PM |