A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Multiple Conditional Formats for A Chart (Please Help)?



 
 
Thread Tools Display Modes
  #1  
Old September 19th, 2003, 09:40 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default Multiple Conditional Formats for A Chart (Please Help)?

Actually, functions might be the right place for your question. Here,
"chart" refers to a graphical presentation of worksheet data, and I
think you are using "chart" to refer to a tabular display of data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

dkenebre wrote:
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:PG1.
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/attach...?postid=270606 (achart question1b.xls)

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.