conditional formatting in an IF function?

[Melvin Tang]

I have a set of specified values in two columns in worksheet 1: M1 = 1, M2 =
2, M3 = 3 etc... in Worksheet 2, i have two columns: Class (M1 to M10) and
Value (1 to 10). How do I, in Worksheet 2, highlight the cells that don't
match the specified value of the class (ie. M1 = 5)?

 

[Max]

In Sheet1,
you have the 2 col base references in A1:B1 down, eg:
M1 1
M2 2
etc

Then in Sheet2,
you have data like this in A1:B1 down, eg:
M2 2
M1 2
etc

Select cols A & B (with A1 active), then apply CF using Formula Is:
=$B1<>VLOOKUP($A1,INDIRECT("'Sheet1'!A:B"),2,0)
Format to taste > OK out. This should return the desired CF highlights, eg
the 2nd row in the sample data above: M1 2 would appear conditionally
formatted as it differs from the base ref in Sheet1: M1 1.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik