Conditional format and multiple variables
 

I have a simple spreadsheet with a value in column J (our price). Columns K
through R are the prices our competitors charge for the item. I need to
compare column J to the others and shade J if the price is less than or equal
to any of the values in K through R.

In addition, I need to shade any value in K through R if it equals J.

One possible hitch, column J is driven by column F (formula in J is "=f4)
etc..

Any help would be appreciated. I have read the sections in my Excel 2003
guide on conditional formatting, but not having much luck.

Thanks,

Jon

Conditional format and multiple variables
 

shade J if the price is less than or equal to
any of the values in K through R.

Are you sure you didn't mean less than or equal to *all* of the other
values?

Here's how to do it for *any*:

Assume your data is in the range J2:R10

Select the range J2:J10
Goto the menu FormatConditional Formatting
Formula Is: =COUNTIF(K2:R2,"="&J2)0
Click the Format button
Select the style(s) desired
OK out

Select the range K2:R10
Goto the menu FormatConditional Formatting
Formula Is: =AND(K2<"",K2=$J2)
Click the Format button
Select the style(s) desired
OK out

--
Biff
Microsoft Excel MVP


"jmcclain" wrote in message
...
I have a simple spreadsheet with a value in column J (our price). Columns
K
through R are the prices our competitors charge for the item. I need to
compare column J to the others and shade J if the price is less than or
equal
to any of the values in K through R.

In addition, I need to shade any value in K through R if it equals J.

One possible hitch, column J is driven by column F (formula in J is "=f4)
etc..

Any help would be appreciated. I have read the sections in my Excel 2003
guide on conditional formatting, but not having much luck.

Thanks,

Jon

Conditional format and multiple variables
 

Thanks very much - but it isn't working exactly as I need.

Assuming data range is k4:R4, I need it to only shade the lowest value(s) if
the value is less than the value in J4. If no value in the range is <= to
K4, then shade K4.

Any help is appreciated...

"T. Valko" wrote:

shade J if the price is less than or equal to
any of the values in K through R.

Are you sure you didn't mean less than or equal to *all* of the other
values?

Here's how to do it for *any*:

Assume your data is in the range J2:R10

Select the range J2:J10
Goto the menu FormatConditional Formatting
Formula Is: =COUNTIF(K2:R2,"="&J2)0
Click the Format button
Select the style(s) desired
OK out

Select the range K2:R10
Goto the menu FormatConditional Formatting
Formula Is: =AND(K2<"",K2=$J2)
Click the Format button
Select the style(s) desired
OK out

--
Biff
Microsoft Excel MVP


"jmcclain" wrote in message
...
I have a simple spreadsheet with a value in column J (our price). Columns
K
through R are the prices our competitors charge for the item. I need to
compare column J to the others and shade J if the price is less than or
equal
to any of the values in K through R.

In addition, I need to shade any value in K through R if it equals J.

One possible hitch, column J is driven by column F (formula in J is "=f4)
etc..

Any help would be appreciated. I have read the sections in my Excel 2003
guide on conditional formatting, but not having much luck.

Thanks,

Jon

Conditional format and multiple variables
 

Ok, Try these:

J4
Formula Is:
=AND(J4<"",J4=MIN(J4:R4))

K4:R4
Formula Is:
=AND(K4<"",K4=MIN($J4:$R4))


--
Biff
Microsoft Excel MVP


"jmcclain" wrote in message
...
Thanks very much - but it isn't working exactly as I need.

Assuming data range is k4:R4, I need it to only shade the lowest value(s)
if
the value is less than the value in J4. If no value in the range is <= to
K4, then shade K4.

Any help is appreciated...

"T. Valko" wrote:

shade J if the price is less than or equal to
any of the values in K through R.

Are you sure you didn't mean less than or equal to *all* of the other
values?

Here's how to do it for *any*:

Assume your data is in the range J2:R10

Select the range J2:J10
Goto the menu FormatConditional Formatting
Formula Is: =COUNTIF(K2:R2,"="&J2)0
Click the Format button
Select the style(s) desired
OK out

Select the range K2:R10
Goto the menu FormatConditional Formatting
Formula Is: =AND(K2<"",K2=$J2)
Click the Format button
Select the style(s) desired
OK out

--
Biff
Microsoft Excel MVP


"jmcclain" wrote in message
...
I have a simple spreadsheet with a value in column J (our price).
Columns
K
through R are the prices our competitors charge for the item. I need
to
compare column J to the others and shade J if the price is less than or
equal
to any of the values in K through R.

In addition, I need to shade any value in K through R if it equals J.

One possible hitch, column J is driven by column F (formula in J is
"=f4)
etc..

Any help would be appreciated. I have read the sections in my Excel
2003
guide on conditional formatting, but not having much luck.

Thanks,

Jon

Conditional format and multiple variables
 

THANK YOU so much...

Not only did this correct the current problem, but I now understand the
syntax of these arguments...

Again - much appreciation.

"T. Valko" wrote:

Ok, Try these:

J4
Formula Is:
=AND(J4<"",J4=MIN(J4:R4))

K4:R4
Formula Is:
=AND(K4<"",K4=MIN($J4:$R4))


--
Biff
Microsoft Excel MVP


"jmcclain" wrote in message
...
Thanks very much - but it isn't working exactly as I need.

Assuming data range is k4:R4, I need it to only shade the lowest value(s)
if
the value is less than the value in J4. If no value in the range is <= to
K4, then shade K4.

Any help is appreciated...

"T. Valko" wrote:

shade J if the price is less than or equal to
any of the values in K through R.

Are you sure you didn't mean less than or equal to *all* of the other
values?

Here's how to do it for *any*:

Assume your data is in the range J2:R10

Select the range J2:J10
Goto the menu FormatConditional Formatting
Formula Is: =COUNTIF(K2:R2,"="&J2)0
Click the Format button
Select the style(s) desired
OK out

Select the range K2:R10
Goto the menu FormatConditional Formatting
Formula Is: =AND(K2<"",K2=$J2)
Click the Format button
Select the style(s) desired
OK out

--
Biff
Microsoft Excel MVP


"jmcclain" wrote in message
...
I have a simple spreadsheet with a value in column J (our price).
Columns
K
through R are the prices our competitors charge for the item. I need
to
compare column J to the others and shade J if the price is less than or
equal
to any of the values in K through R.

In addition, I need to shade any value in K through R if it equals J.

One possible hitch, column J is driven by column F (formula in J is
"=f4)
etc..

Any help would be appreciated. I have read the sections in my Excel
2003
guide on conditional formatting, but not having much luck.

Thanks,

Jon

Conditional format and multiple variables
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jmcclain" wrote in message
...
THANK YOU so much...

Not only did this correct the current problem, but I now understand the
syntax of these arguments...

Again - much appreciation.

"T. Valko" wrote:

Ok, Try these:

J4
Formula Is:
=AND(J4<"",J4=MIN(J4:R4))

K4:R4
Formula Is:
=AND(K4<"",K4=MIN($J4:$R4))


--
Biff
Microsoft Excel MVP


"jmcclain" wrote in message
...
Thanks very much - but it isn't working exactly as I need.

Assuming data range is k4:R4, I need it to only shade the lowest
value(s)
if
the value is less than the value in J4. If no value in the range is <=
to
K4, then shade K4.

Any help is appreciated...

"T. Valko" wrote:

shade J if the price is less than or equal to
any of the values in K through R.

Are you sure you didn't mean less than or equal to *all* of the other
values?

Here's how to do it for *any*:

Assume your data is in the range J2:R10

Select the range J2:J10
Goto the menu FormatConditional Formatting
Formula Is: =COUNTIF(K2:R2,"="&J2)0
Click the Format button
Select the style(s) desired
OK out

Select the range K2:R10
Goto the menu FormatConditional Formatting
Formula Is: =AND(K2<"",K2=$J2)
Click the Format button
Select the style(s) desired
OK out

--
Biff
Microsoft Excel MVP


"jmcclain" wrote in message
...
I have a simple spreadsheet with a value in column J (our price).
Columns
K
through R are the prices our competitors charge for the item. I
need
to
compare column J to the others and shade J if the price is less than
or
equal
to any of the values in K through R.

In addition, I need to shade any value in K through R if it equals
J.

One possible hitch, column J is driven by column F (formula in J is
"=f4)
etc..

Any help would be appreciated. I have read the sections in my Excel
2003
guide on conditional formatting, but not having much luck.

Thanks,

Jon

Conditional format and multiple variables
 

I hate to bug you again, but how can I make the value in J not shade if there
are no values in k:R?



"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jmcclain" wrote in message
...
THANK YOU so much...

Not only did this correct the current problem, but I now understand the
syntax of these arguments...

Again - much appreciation.

"T. Valko" wrote:

Ok, Try these:

J4
Formula Is:
=AND(J4<"",J4=MIN(J4:R4))

K4:R4
Formula Is:
=AND(K4<"",K4=MIN($J4:$R4))


--
Biff
Microsoft Excel MVP


"jmcclain" wrote in message
...
Thanks very much - but it isn't working exactly as I need.

Assuming data range is k4:R4, I need it to only shade the lowest
value(s)
if
the value is less than the value in J4. If no value in the range is <=
to
K4, then shade K4.

Any help is appreciated...

"T. Valko" wrote:

shade J if the price is less than or equal to
any of the values in K through R.

Are you sure you didn't mean less than or equal to *all* of the other
values?

Here's how to do it for *any*:

Assume your data is in the range J2:R10

Select the range J2:J10
Goto the menu FormatConditional Formatting
Formula Is: =COUNTIF(K2:R2,"="&J2)0
Click the Format button
Select the style(s) desired
OK out

Select the range K2:R10
Goto the menu FormatConditional Formatting
Formula Is: =AND(K2<"",K2=$J2)
Click the Format button
Select the style(s) desired
OK out

--
Biff
Microsoft Excel MVP


"jmcclain" wrote in message
...
I have a simple spreadsheet with a value in column J (our price).
Columns
K
through R are the prices our competitors charge for the item. I
need
to
compare column J to the others and shade J if the price is less than
or
equal
to any of the values in K through R.

In addition, I need to shade any value in K through R if it equals
J.

One possible hitch, column J is driven by column F (formula in J is
"=f4)
etc..

Any help would be appreciated. I have read the sections in my Excel
2003
guide on conditional formatting, but not having much luck.

Thanks,

Jon

Conditional format and multiple variables
 

Try this:

J4
Formula Is:
=AND(J4<"",COUNT(K4:R4)0,J4=MIN(J4:R4))


--
Biff
Microsoft Excel MVP


"jmcclain" wrote in message
...
I hate to bug you again, but how can I make the value in J not shade if
there
are no values in k:R?



"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jmcclain" wrote in message
...
THANK YOU so much...

Not only did this correct the current problem, but I now understand the
syntax of these arguments...

Again - much appreciation.

"T. Valko" wrote:

Ok, Try these:

J4
Formula Is:
=AND(J4<"",J4=MIN(J4:R4))

K4:R4
Formula Is:
=AND(K4<"",K4=MIN($J4:$R4))


--
Biff
Microsoft Excel MVP


"jmcclain" wrote in message
...
Thanks very much - but it isn't working exactly as I need.

Assuming data range is k4:R4, I need it to only shade the lowest
value(s)
if
the value is less than the value in J4. If no value in the range is
<=
to
K4, then shade K4.

Any help is appreciated...

"T. Valko" wrote:

shade J if the price is less than or equal to
any of the values in K through R.

Are you sure you didn't mean less than or equal to *all* of the
other
values?

Here's how to do it for *any*:

Assume your data is in the range J2:R10

Select the range J2:J10
Goto the menu FormatConditional Formatting
Formula Is: =COUNTIF(K2:R2,"="&J2)0
Click the Format button
Select the style(s) desired
OK out

Select the range K2:R10
Goto the menu FormatConditional Formatting
Formula Is: =AND(K2<"",K2=$J2)
Click the Format button
Select the style(s) desired
OK out

--
Biff
Microsoft Excel MVP


"jmcclain" wrote in message
...
I have a simple spreadsheet with a value in column J (our price).
Columns
K
through R are the prices our competitors charge for the item. I
need
to
compare column J to the others and shade J if the price is less
than
or
equal
to any of the values in K through R.

In addition, I need to shade any value in K through R if it
equals
J.

One possible hitch, column J is driven by column F (formula in J
is
"=f4)
etc..

Any help would be appreciated. I have read the sections in my
Excel
2003
guide on conditional formatting, but not having much luck.

Thanks,

Jon