Conditional Format a column based on another
 

Column A has 100 entries of either 1,2 or 3 which are not necessarily sorted
in order depending upon sorting in other columns but I want the minimum value
in column B based on column A to remain conditionally formatted. i.e. Column
B will always have three cells highlighted.

Conditional Format a column based on another
 

What is in column B, and why 3 highlighted.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Karm" wrote in message
...
Column A has 100 entries of either 1,2 or 3 which are not necessarily
sorted
in order depending upon sorting in other columns but I want the minimum
value
in column B based on column A to remain conditionally formatted. i.e.
Column
B will always have three cells highlighted.

Conditional Format a column based on another
 

Bob

Thanks for the prompt response.

Column B has numbers of which I want to highlight the minimum values. With
a limit of three values in Column A, there is then three values to highlight
in Column B.

"Bob Phillips" wrote:

What is in column B, and why 3 highlighted.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Karm" wrote in message
...
Column A has 100 entries of either 1,2 or 3 which are not necessarily
sorted
in order depending upon sorting in other columns but I want the minimum
value
in column B based on column A to remain conditionally formatted. i.e.
Column
B will always have three cells highlighted.

Conditional Format a column based on another
 

If I read it correctly, you want to highlight the minimum value in B where A
=1, then where A=2, etc.

If so, I would add 3 formulae to say M1:M3, of

=MIN(IF($A$1:$A$30=ROW(A1),$B$1:$B$30))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Then in the CF, use a formula of

=OR(AND(A1=1,B1=$M$1),AND(A1=2,B1=$M$2),AND(A1=3,B 1=$M$3))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Karm" wrote in message
...
Bob

Thanks for the prompt response.

Column B has numbers of which I want to highlight the minimum values.
With
a limit of three values in Column A, there is then three values to
highlight
in Column B.

"Bob Phillips" wrote:

What is in column B, and why 3 highlighted.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Karm" wrote in message
...
Column A has 100 entries of either 1,2 or 3 which are not necessarily
sorted
in order depending upon sorting in other columns but I want the
minimum
value
in column B based on column A to remain conditionally formatted. i.e.
Column
B will always have three cells highlighted.

Conditional Format a column based on another
 

Thanks Bob but not quite;

Assume for the exercise the following values are in column A:
1,1,1,3,3,2,1,2,3,2
and these values are in column B:
10*,12,13,14,13*,12,14,11*,15,14 with * indicating which should be
highlighted as they are the minimum for the group in the corresponding column
A.

"Bob Phillips" wrote:

If I read it correctly, you want to highlight the minimum value in B where A
=1, then where A=2, etc.

If so, I would add 3 formulae to say M1:M3, of

=MIN(IF($A$1:$A$30=ROW(A1),$B$1:$B$30))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Then in the CF, use a formula of

=OR(AND(A1=1,B1=$M$1),AND(A1=2,B1=$M$2),AND(A1=3,B 1=$M$3))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Karm" wrote in message
...
Bob

Thanks for the prompt response.

Column B has numbers of which I want to highlight the minimum values.
With
a limit of three values in Column A, there is then three values to
highlight
in Column B.

"Bob Phillips" wrote:

What is in column B, and why 3 highlighted.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Karm" wrote in message
...
Column A has 100 entries of either 1,2 or 3 which are not necessarily
sorted
in order depending upon sorting in other columns but I want the
minimum
value
in column B based on column A to remain conditionally formatted. i.e.
Column
B will always have three cells highlighted.

Conditional Format a column based on another
 

Hey, guess what, that is exactly what my solution comes up with!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Karm" wrote in message
...
Thanks Bob but not quite;

Assume for the exercise the following values are in column A:
1,1,1,3,3,2,1,2,3,2
and these values are in column B:
10*,12,13,14,13*,12,14,11*,15,14 with * indicating which should be
highlighted as they are the minimum for the group in the corresponding
column
A.

"Bob Phillips" wrote:

If I read it correctly, you want to highlight the minimum value in B
where A
=1, then where A=2, etc.

If so, I would add 3 formulae to say M1:M3, of

=MIN(IF($A$1:$A$30=ROW(A1),$B$1:$B$30))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

Then in the CF, use a formula of

=OR(AND(A1=1,B1=$M$1),AND(A1=2,B1=$M$2),AND(A1=3,B 1=$M$3))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Karm" wrote in message
...
Bob

Thanks for the prompt response.

Column B has numbers of which I want to highlight the minimum values.
With
a limit of three values in Column A, there is then three values to
highlight
in Column B.

"Bob Phillips" wrote:

What is in column B, and why 3 highlighted.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Karm" wrote in message
...
Column A has 100 entries of either 1,2 or 3 which are not
necessarily
sorted
in order depending upon sorting in other columns but I want the
minimum
value
in column B based on column A to remain conditionally formatted.
i.e.
Column
B will always have three cells highlighted.

Conditional Format a column based on another
 

Yes, Bob you're right. It works brilliantly!...Thanks

"Bob Phillips" wrote:

Hey, guess what, that is exactly what my solution comes up with!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Karm" wrote in message
...
Thanks Bob but not quite;

Assume for the exercise the following values are in column A:
1,1,1,3,3,2,1,2,3,2
and these values are in column B:
10*,12,13,14,13*,12,14,11*,15,14 with * indicating which should be
highlighted as they are the minimum for the group in the corresponding
column
A.

"Bob Phillips" wrote:

If I read it correctly, you want to highlight the minimum value in B
where A
=1, then where A=2, etc.

If so, I would add 3 formulae to say M1:M3, of

=MIN(IF($A$1:$A$30=ROW(A1),$B$1:$B$30))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

Then in the CF, use a formula of

=OR(AND(A1=1,B1=$M$1),AND(A1=2,B1=$M$2),AND(A1=3,B 1=$M$3))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Karm" wrote in message
...
Bob

Thanks for the prompt response.

Column B has numbers of which I want to highlight the minimum values.
With
a limit of three values in Column A, there is then three values to
highlight
in Column B.

"Bob Phillips" wrote:

What is in column B, and why 3 highlighted.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Karm" wrote in message
...
Column A has 100 entries of either 1,2 or 3 which are not
necessarily
sorted
in order depending upon sorting in other columns but I want the
minimum
value
in column B based on column A to remain conditionally formatted.
i.e.
Column
B will always have three cells highlighted.