• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Long time reader! First time poster. (conditional formatting hurts me)

dan_l

Active Member
So, I made my first 'dashboard' that doesn't fail miserably.


Seriously. I finally learned manned up and learned sparklines (not as hard as it looks) and got cute with the datavalidation/lookup trick. The end result provides a ytd overview worksheet and a monthly 'report'.


The monthly report includes a list of sales people along with their total sales numbers. So


A |b |c

1 Jim Dewey 9000

2 John Cheatum 10000

3 Mike Howe 11000


So, what do Dewey Cheatum and Howe need? Some balls, of course.


I found the alt code for the filled in circle (U+25CF). I want to make a green colored ball for the top sales guy and a red colored ball for the bottom sales guy.


So my working theory here is this:

1. The cells where the character will be displayed should have the character in it no matter what. The formatting should be white/white so that it will be invisible without a conditional formatting formula being true. Assume the ball is in place in column C.


2. Apply a conditional formatting formula which checks column B per row to determine if it's the largest value in the list and colors the text in the appropriate row of column C. So in my head, the formula should look like:


=b1=(large(b$1:b$3,1))


That doesn't work, but it's pretty literal to what I'm trying to do. I've tried parsing it out just in an if statement on the worksheet and that can work with if(b1=large(b1:b3,1),"PASS","FAIL") but I can't seem to express the logic in conditional formatting.


Right now, I'm stuck with something pretty duct tape. Basically, my source/calc sheet makes the determination whether or not the ball should be there with a nested if statement and a = reference on the report sheet. Of course that doesn't color code it the way that I want.
 
Welcome to PHD Forums. we have been waiting for you.


Ofcourse they need balls ;)


here is how you can do it... Assuming column C has balls in it,


Remove any CF rules from Column C - Edit menu > clear > formats (in Excel 2007 > home > clear > formats)

now select all the cells in column C and whitewash them.

Now go to CF

use formula type rule

the rule is =$B1=MAX($B$1:$B$3)

in the formats tab set the color

Enjoy


Btw you can hide the cell contents much more elegantly using custom cell format code ;;; (read this: http://chandoo.org/wp/2009/06/05/hide-cell/ )
 
> Btw you can hide the cell contents much more elegantly using custom cell format code ;;;


If you do that, you will also have to set a different number format in the CF-dialog, or the balls will stay hidden, even though they are now green!
 
And......it works.


I'm going to need to really learn conditional formatting.


I've never actually had much call for it. It's always been a level of polish I've never had to put into practice....till I started messing with dashboards.


Thanks folks!
 
Back
Top