Condition Format #1

[dkenebre]

I have enclosed a chart with this explanation below. I am needing
automate this chart according to the following rules.
See 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

File Attached: http://www.exceltip.com/forum/attachment.php?postid=269808 (achart question1b.xls)

 

[mk]

The example spreadsheet cannot be found on excel.tip.
But maybe I have found a solution to the 'first' request.
Already sent to your email -address - sorry but it's too complicated to
explain
on groups - if anyone wants to see my how it works - let me know to my
email.
(please remove .bleble from my email address)
regards

 

[dkenebre]

Thanks MK for your reply, although, if you sent me an email, I did not
get it. Here is my email address: (e-mail address removed). So at the
moment I still have no solution. I wasn't clear with your reply but if
you need to look at my sample spreadsheet, I have re-attach it to this
post.

File Attached: http://www.exceltip.com/forum/attachment.php?postid=270239 (achart question1b.xls)

 

[dvt]

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)

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 P1G1.
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 B11 to accomplish that.

That's enough for now. I haven't attempted to highlight the proper cells
blue. You have some tough nuts to crack (also in the Charting newsgroup),
and you may be lucky to get this one cracked for free.

Dave
dvt at psu dot edu

 

[dkenebre]

DVT Thanks for your help,
This conditional formula generates red in every cell, I am looking at
only the correct boxed pairs. So let me explain the first part of my
problem a different way:

Every time a digit is entered into cells B, C and D (1&2), take B and C
and find it’s equivalent in cells E through AW, even if the two digits
are in reversed order, then highlight that cell RED (ie. B19=2 and
C19=0, then F19=02, which is equal to B19 & C19 in reverse, so F19
equals B19&C19. F19 is highlighted RED

Now repeat this logic with C and D (2&3) (ie. C19=0 and D19=5, so
I19=05 which is equal to C19&D19 in the same order, therefore I19 is
highlighted RED

Now repeat this logic with B and D (1&3) (ie. B19=2 and D19=5, so
X19=25 which is equal to B19&D19 in the same order, therefore X19 is
highlighted RED

Second Part
In the cases were any two of these three cell combinations B&C, C&D and
B&D have a double digit combination, then only the non-double digit
cell pairing combination is highlighted RED, in the identical E through
AW cell, which is in the same order or reversed. (ie. B7=4, C7=2, D7=2:
E through AW have no double digit cells, therefore only 42 is valid,
which equals W7=24 in reverse order in the chart sample. W7 is
highlighted RED)
Third Part
Also, if neither B&C or C&D or B&D do not equal any cells in E through
AW in the same row, then all cells in E through AW on that row are
highlighted BLACK. (ie. 7,7,7 E through AW have no double digit cells)
See chart

Maybe to simply the blue highlight issue, I will initially set the E
through AW cells blue to begin with and whenever the red highlight
conditions apply it will override and change the blue to red.

Hopefully someone can tell me the best approach to automate this
chart.

see chart attachment

File Attached: http://www.exceltip.com/forum/attachment.php?postid=270868 (achart question1b.xls)

 

[dvt]

DVT Thanks for your help,
This conditional formula generates red in every cell, I am looking at
only the correct boxed pairs.

Not when I enter it. Are you certain that all cells are formatted as
numbers, rather than text?

Every time a digit is entered into cells B, C and D (1&2), take B and
C
and find it’s equivalent in cells E through AW, even if the two digits
are in reversed order, then highlight that cell RED (ie. B19=2 and
C19=0, then F19=02, which is equal to B19 & C19 in reverse, so F19
equals B19&C19. F19 is highlighted RED

B19=2, C19=0. 10*B19 + C19 = 20. 10*C10 + B19 = 2. If your cells are
formatted as numbers, the formula I gave should color the cell red if and
only if the cell contains a combination of the two specified digits. It
holds true for the other examples you gave.

Second Part
In the cases were any two of these three cell combinations B&C, C&D
and
B&D have a double digit combination, then only the non-double digit
cell pairing combination is highlighted RED, in the identical E
through
AW cell, which is in the same order or reversed. (ie. B7=4, C7=2,
D7=2:
E through AW have no double digit cells, therefore only 42 is valid,
which equals W7=24 in reverse order in the chart sample. W7 is
highlighted RED)

In this example, the formula I gave will highlight a cell with the value 22
entered. You will have to come up with your own logic for that one. Maybe
you could use data validation on columns B, C, and D.

Third Part
Also, if neither B&C or C&D or B&D do not equal any cells in E through
AW in the same row, then all cells in E through AW on that row are
highlighted BLACK. (ie. 7,7,7 E through AW have no double digit cells)
See chart

Once again, that is a condition that I don't have the time to address.

Maybe to simply the blue highlight issue, I will initially set the E
through AW cells blue to begin with and whenever the red highlight
conditions apply it will override and change the blue to red.

That would work.

Dave
dvt at psu dot edu

 

[dkenebre]

Okay dvt, you were correct. I got it to work, but I also need this
formula to make the cell RED if the 2 different digits are in reversed
direction. (ie. 635 = 35 in the same order and = 36 in reverse
direction and also = to 56 boxed pair)
Then I need to make formula work for the double digit section.

I have simplified my explanation and updated my request from a previous
post on formatting: (See Chart Update for illustration.)
Okay, I have 3 sections to this chart, Number combination section,
double digits and boxed pairs.
The goal is to automate this chart whenever a 3 digit number is entered
into the spreadsheet it searches the double digit columns for a double
digit, if it applies then that double digit is highlighted YELLOW in
the double digit section, also another formula is used to search the
boxed pairs section for the 3 boxed pairs if the 3 digits do not have a
double digit, if it does then the formula will only search for the 2
boxed pairs in this section and in either case those cells are
highlighted RED.
Finally if the 3 digit numbers entered into the spreadsheet are all the
same digits (ie.7,7,7), then the double digit is highlighted BLACK,
with white font in the double digit section, instead of yellow. But if
it is simpler highlight the entire section on that row in black
highlight w/ white font, rather than only the double digit cell. Also,
the entire boxed pair section on this row is highlighted BLACK w/White
font.

File Attached: http://www.exceltip.com/forum/attachment.php?postid=274029 (achart question1.xls)

 

[dkenebre]

Dvt, your conditional formatting formula works for the double digit
section.
All I need is the following for the double digit section:

A conditional formula that if all 3 cells are equal and is also equal a
double digit cell then that cell will be BLACK.
example:
a1=7+b1=7+c1=7 , then k1=77 is automatically highlighted BLACK
(d1=00, e1=11, f1=22, g1=33, h1=44, i1=55, j1=66, k1=77, l1=88 and
m1=99)

 

[dvt]

I need... the following for the double digit section:

A conditional formula that if all 3 cells are equal and is also equal
a double digit cell then that cell will be BLACK.
example:
a1=7+b1=7+c1=7 , then k1=77 is automatically highlighted BLACK
(d1=00, e1=11, f1=22, g1=33, h1=44, i1=55, j1=66, k1=77, l1=88 and
m1=99)

Try this:
=AND($B$1=$C$1,$C$1=$D$1,AF1=$B$1*10+$C$1)

It first checks to be sure that all 3 cells are the same, then checks to see
if the current cell value is a double-digit combo of that digit.

Put that in condition #1, the other (red) formula in condition #2. Both
conditions will evaluate as true, but in my spreadsheet (XL XP), the first
condition to evaluate as true wins.

Dave
dvt at psu dot edu

 

[dkenebre]

Okay, DVT, we are getting close with this problem. Your latest formula
worked for the double digit section. Good Job.

What about the following 2 conditions:

1st condition:
Now what would the conditional formula be,
if cells B, C and D are equal or the same (ie 7,7,7), then change cells
E through AW to be black background w/white font.

2nd condition:
Compare/combine cell’s value B&C or C&B, C&D or D&C and B&D or D&B, if
any of these combinations equal the value in cell E, then Highlight
cell E RED.

 

[dvt]

1st condition:
Now what would the conditional formula be,
if cells B, C and D are equal or the same (ie 7,7,7), then change
cells E through AW to be black background w/white font.

I pretty much gave you that one already. Something like the untested
=AND(b1=c1,c1=d1)
in the conditional format of E1:AW1 should work.

2nd condition:
Compare/combine cell’s value B&C or C&B, C&D or D&C and B&D or D&B, if
any of these combinations equal the value in cell E, then Highlight
cell E RED.

I thought we did that one already, too. The formula to highlight the cells
red looked something like this:

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

Obviously you need to change the O1 to E1, or perhaps copy O1 and paste
special | Formats to cell E1.

Dave
dvt at psu dot edu

PS can you please reply to the appropriate messages so your message threads
properly? thanks.