Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional format based on other cells | Excel Discussion (Misc queries) | |||
Conditional Format based on other cell's value | Excel Worksheet Functions | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
Format cell in column B based on value in the next cell (column c) | Excel Discussion (Misc queries) |