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.
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.
