Conditional Formatting on Index,Match formula
 

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?

Conditional Formatting on Index,Match formula
 

I am using Excel 2003.

Try using the formula Is option in conditional formatting.

Use this formula:

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

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
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?

Conditional Formatting on Index,Match formula
 

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" wrote:

I am using Excel 2003.

Try using the formula Is option in conditional formatting.

Use this formula:

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

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
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?


.

Conditional Formatting on Index,Match formula
 

Did you mean to choose "Is equal to"

No

You said you're using Excel 2003

So...

Select cell S6
Goto the menu FormatConditional 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

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
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" wrote:

I am using Excel 2003.

Try using the formula Is option in conditional formatting.

Use this formula:

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

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
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?


.