Conditional Formatting on Index,Match formula

N

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

T. Valko

I am using Excel 2003.

Try using the formula Is option in conditional formatting.

Use this formula:

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

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top