Conditional Formatting on Index,Match formula

[Nadine]

I am using Excel 2003.

I am trying to use Conditional Format for a cell that contains the following
Index, Match formula:
=IF(ISNA(INDEX('PO20-04-30-10'!S:S,MATCH('04-30-10-ByPO'!A6,'PO20-04-30-10'!B:B,0))),"",(INDEX('PO20-04-30-10'!S:S,MATCH('04-30-10-ByPO'!A6,'PO20-04-30-10'!B:B,0))))

This formula is in cell S6. I'd like it to turn yellow if the result of the
formula is less than the amount in cell H6 which is a total column in a pivot
table. When I try to do this, the result never works. I've tried greater
than another cell, less than H6, greater than 0, not equal to "", etc. I
can't get this to turn yellow under the right condition. Any ideas?

 

[T. Valko]

I am using Excel 2003.

Try using the formula Is option in conditional formatting.

Use this formula:

=AND(COUNT(S6),S6<H6)

 

[Nadine]

Did you mean to choose "Is equal to" and then enter =AND(COUNT(S6),S6<H6) in
the Conditional Format dialog box? If so, this didn't work. Thanks for
trying.

 

[T. Valko]

Did you mean to choose "Is equal to"

No

You said you're using Excel 2003

So...

Select cell S6
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=AND(COUNT(S6),S6<H6)
Click the Format button
Select the desired style(s)
OK out