In Excel 2000, Im told that I cant reference other worksheets or workbooks
when using conditional formatting. Looking through these forums, I put
together a formula that highlights cells if there are duplicate listings of
companies found in column S (row 2 has headers and the companies are listed
in cells S3:S261. The conditional format in this €œRates.xls€� file for S3,
for example, is:
Formula Is =COUNTIF(S:S,S3)1.

I want to use a similar conditional format to highlight cells in column G of
my other workbook (Test Rate) whereby I find pay rates from the €œRates€� file
based on TWO criteria: column E (company name) that matches values in column
S of the €œRates€� file; and column F, which concatenates columns B (Type), C
(Program), & D (Model) with /s in the €œTest Rate€� file to match the values
found in column K of the €œRates€� file. Since Im told that I cant reference
other worksheets or workbooks in the conditional format, Ive created a
formula in column L which basically checks to see if BOTH conditions are met
and returns either True or False. The formula in cell L8 is:
=AND(COUNTIF('[Rates.xls]Sheet1'!$S:$S,$E8)1,COUNTIF('[Rates.xls]Sheet1'!$K:$K,$F8)1)

My problem is that this formula is evaluating each condition independently
and I need it to evaluate BOTH conditions TOGETHER. Currently this formula
is incorrectly returning TRUE for an entry that should be FALSE because
although there is 1 company listing in the €œRates€� file (this evaluates as
TRUE), AND there is 1 listing of the value in F8 (Networks/Res/Home; which
also evaluates as TRUE), the combination of both items together results in
only 1 value for each combination.

In other words, even though there are 2 listings for €œCompany A€�, the values
in column F are different for each of the 2 listings. Column F of one of
Company As listings is Networks/Res/School (of which there are 58 listings)
and in the other of Company As listings, column F is Networks/Res/Home (of
which there are 70 listings). Because the formula is currently evaluating
each condition independently, the 2nd half of the formula is returning TRUE
because there are multiple listings for this half, but evaluating BOTH
conditions TOGETHER, the formula should return FALSE. How do I correct my
formula?