• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting

T

trianna

New Member
Hello all,


I hope that someone will be able to help me resolve a conditional formatting issue.


I have a spreadsheet template that is used by a number of users for scheduling and tracking classes. The sheet can contain over 800 row entries with over a 200 different classes available each with own course length.


To resolve an ongoing issue with users entering incorrect course length a lookup table consisting of many of available class titles is used to verify the course length and if it is incorrect the cell colors to red.


It works as follows;


Column A contains the class title

Column D is the class length entered by the user

Column E is a hidden helper column that references the lookup table and enters the class length based on the class title entered in column A by the user. If no matching title is found it will enter NA.


=IF(ISNA(VLOOKUP(A10,'Class Title'!C$3:D$132,2,FALSE)),"NA",VLOOKUP(A10,'Course Title'!C$2:D$132,2,FALSE))


My problem is that I need to conditionally format each cell of the possible 800 rows in column D to color format to red if the class length is not equal to the cell value in column E or color format to no fill if column E is NA.


I know that I can use the following conditional formatting rules to accomplish this but with over 800 rows and two conditions, it is overwhelming.


I hope that I have explained this well and that someone could please tell me this is possible and how to do it.


* To support these users the template was developed in Excel 2007 but saved to Excel 2003.


Thanks,


Trianna
 
Try the following:

Select D2:D800, then apply the red fill conditional formatting using the following formula:

=AND($D2<>$E2,$E2<>"NA")

(Note the relative row references.)

I am assuming that your default formatting is no fill.
 
What happens when you apply both of these rules as conditional formats in col D (in other words, what's not working?)

Formula 1:

=E2="NA"

Format clear


formula 2:

=E2<>D2

Format red
 
ruve1k,


Thank you very much!!!


It worked perfectly. So well infact I was then able to use that knowledge in other areas of the spread sheet.


Thank you again.


Trianna
 
You must log in or register to reply here.
Back
Top