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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 | |
|
|