D
dkenebre
Condition Format #1
I submitted this post to the misc. and functions group not considering
that the charting group is properly the best place to submit this post
so hear goes you charting experts I would appreciate your assistance.
Please disregard this post on either of those newsgroups as it would be
redundant.
I have enclosed a chart with this explanation below. I am needing to
automate this chart according to the following rules.
See attached sample chart also, if necessary. Thank you.
First
Whenever, a 3 digit numeric combination is entered into B, C and D
cells and if each of the 3 digits are different (ie.3,7,2), then find
the 3 cells on the same row between O and BG that have the
corresponding boxed the 2 digit pairs. (O=01, P=02, Q=03, R=04, S=05,
T=06, U=07, V=08, W=09, X=12, Y=13, Z=14, AA=15, AB=16, AC=17, AD=18,
AE=19, AF=23, AG=24, AH=25, AI=26, AJ=27, AK=28, AL=29, AM=34, AN=35,
AO=36, AP=37, AQ=38, AR=39, AS=45, AT=46, AU=47, AV=48, AW=49, AX=56,
AY=57, AZ=58, BA=59, BB=67, BC=68, BD=69, BE=78, BF=79, BG=89) and
highlight the 3 cells RED, then all of the previous cells in each of
those 3 columns that is not highlighted RED or BLACK will automatically
be highlighted blue.
Example A: 4,1,9 (z=14, AE=19 and AW=49 get highlighted RED, all the
previous cells in these 3 columns which are highlighted white are
changed to blue)
Second
Whenever a 3 digit numeric combination is entered into B, C and D cells
and if 2 of 3 the digits are the same (ie. 3,7,7), then find the one
cell on the same row between O and BG that has the corresponding boxed
2 digit pair, then highlight that cell RED and then all of the previous
cells in that column that is not highlighted RED or BLACK will
automatically be highlighted blue.
Example B: 4,2,2 (AG=24 get highlighted RED, all the previous cells in
this column which are highlighted white are changed to blue)
Third
Whenever a 3 digit numeric combination is entered into B, C and D cells
and all 3 the digits are the same (ie. 7,7,7), then find the cells on
the same row between O and BG and automatically highlight those cells
BLACK and text white, and then all of the previous cells in each column
that is not highlighted RED or BLACK will automatically be highlighted
blue.
Example C: 5,5,5 (All cells on the same row O through BG are highlight
BLACK with white font and all previous cells in each column which are
highlighted white are changed to blue)
If necessary, see sample chart in the Attachment
As a partial solution to the first part of this chart project, I
receive this formula below:
Conditional format cell O1 using a forumula like this...
=($b$1*10+$c$1=O1)+($b$1*10+$D$1=O1)+($c$1*10+$b$1
=o1)+($c$1*10+$D$1=o1)+($D
$1*10+$b$1=o1)+($D$1*10+$c$1=o1)>=1
Now copy cell O1 and Edit | Paste Special | Formats to the range of
P1
G1.
That will take care your "red" condition. Note that this will not
detect
double numbers -- in your example of 3,7,2, this formula will not
highlight
33, 77, and 22. It will also not ensure that the three numbers are
unique.
You probably need data validation on cells B1 1 to accomplish that.
I do have another part to this chart that includes double digits, but I
will submit that later.
File Attached: http://www.exceltip.com/forum/attachment.php?postid=270606 (achart question1b.xls)
I submitted this post to the misc. and functions group not considering
that the charting group is properly the best place to submit this post
so hear goes you charting experts I would appreciate your assistance.
Please disregard this post on either of those newsgroups as it would be
redundant.
I have enclosed a chart with this explanation below. I am needing to
automate this chart according to the following rules.
See attached sample chart also, if necessary. Thank you.
First
Whenever, a 3 digit numeric combination is entered into B, C and D
cells and if each of the 3 digits are different (ie.3,7,2), then find
the 3 cells on the same row between O and BG that have the
corresponding boxed the 2 digit pairs. (O=01, P=02, Q=03, R=04, S=05,
T=06, U=07, V=08, W=09, X=12, Y=13, Z=14, AA=15, AB=16, AC=17, AD=18,
AE=19, AF=23, AG=24, AH=25, AI=26, AJ=27, AK=28, AL=29, AM=34, AN=35,
AO=36, AP=37, AQ=38, AR=39, AS=45, AT=46, AU=47, AV=48, AW=49, AX=56,
AY=57, AZ=58, BA=59, BB=67, BC=68, BD=69, BE=78, BF=79, BG=89) and
highlight the 3 cells RED, then all of the previous cells in each of
those 3 columns that is not highlighted RED or BLACK will automatically
be highlighted blue.
Example A: 4,1,9 (z=14, AE=19 and AW=49 get highlighted RED, all the
previous cells in these 3 columns which are highlighted white are
changed to blue)
Second
Whenever a 3 digit numeric combination is entered into B, C and D cells
and if 2 of 3 the digits are the same (ie. 3,7,7), then find the one
cell on the same row between O and BG that has the corresponding boxed
2 digit pair, then highlight that cell RED and then all of the previous
cells in that column that is not highlighted RED or BLACK will
automatically be highlighted blue.
Example B: 4,2,2 (AG=24 get highlighted RED, all the previous cells in
this column which are highlighted white are changed to blue)
Third
Whenever a 3 digit numeric combination is entered into B, C and D cells
and all 3 the digits are the same (ie. 7,7,7), then find the cells on
the same row between O and BG and automatically highlight those cells
BLACK and text white, and then all of the previous cells in each column
that is not highlighted RED or BLACK will automatically be highlighted
blue.
Example C: 5,5,5 (All cells on the same row O through BG are highlight
BLACK with white font and all previous cells in each column which are
highlighted white are changed to blue)
If necessary, see sample chart in the Attachment
As a partial solution to the first part of this chart project, I
receive this formula below:
Conditional format cell O1 using a forumula like this...
=($b$1*10+$c$1=O1)+($b$1*10+$D$1=O1)+($c$1*10+$b$1
=o1)+($c$1*10+$D$1=o1)+($D
$1*10+$b$1=o1)+($D$1*10+$c$1=o1)>=1
Now copy cell O1 and Edit | Paste Special | Formats to the range of
P1
G1.That will take care your "red" condition. Note that this will not
detect
double numbers -- in your example of 3,7,2, this formula will not
highlight
33, 77, and 22. It will also not ensure that the three numbers are
unique.
You probably need data validation on cells B1 1 to accomplish that.
I do have another part to this chart that includes double digits, but I
will submit that later.
File Attached: http://www.exceltip.com/forum/attachment.php?postid=270606 (achart question1b.xls)