Remember Me?

#1
February 8th 08, 04:33 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 48
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

#2
February 8th 08, 07:25 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
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
Formula Is: =COUNTIF(K2:R2,"="&J2)0
Click the Format button
Select the style(s) desired
OK out

Select the range K2:R10
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

#3
February 8th 08, 07:45 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 48
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

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
Formula Is: =COUNTIF(K2:R2,"="&J2)0
Click the Format button
Select the style(s) desired
OK out

Select the range K2:R10
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

#4
February 8th 08, 08:26 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
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

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
Formula Is: =COUNTIF(K2:R2,"="&J2)0
Click the Format button
Select the style(s) desired
OK out

Select the range K2:R10
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

#5
February 8th 08, 08:43 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 48
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

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
Formula Is: =COUNTIF(K2:R2,"="&J2)0
Click the Format button
Select the style(s) desired
OK out

Select the range K2:R10
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

#6
February 8th 08, 08:46 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
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

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
Formula Is: =COUNTIF(K2:R2,"="&J2)0
Click the Format button
Select the style(s) desired
OK out

Select the range K2:R10
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

#7
February 8th 08, 08:54 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 48
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

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
Formula Is: =COUNTIF(K2:R2,"="&J2)0
Click the Format button
Select the style(s) desired
OK out

Select the range K2:R10
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

#8
February 8th 08, 09:03 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
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

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
Formula Is: =COUNTIF(K2:R2,"="&J2)0
Click the Format button
Select the style(s) desired
OK out

Select the range K2:R10
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Duncs Excel Discussion (Misc queries) 10 July 24th 07 12:01 PM Ang Excel Worksheet Functions 3 April 27th 07 08:24 PM Krista Excel Discussion (Misc queries) 3 May 12th 06 10:58 PM Emile Excel Worksheet Functions 8 March 7th 06 05:27 PM Liketoknow Excel Worksheet Functions 1 November 10th 04 10:34 AM

All times are GMT +1. The time now is 02:00 PM.