Oct 162008
 

Many traffic-light solutions for Excel exist but all the ones I tried only work for a single light, and I needed an array like this:

I wanted to create a light by simply typing a formula in the light’s cell, with the colour of the light determined by a value in cell elsewhere. The light must change colour as soon as the underlying value changes. This wasn’t as simple to implement as I thought, but in the end a bit of VBA led to this formula :
=trafficlight(Sheet2!B4,Sheet2!B$3,Sheet2!C$3)
and the rest of the lights are created by dragging this formula right and down.

The parameters to the TrafficLight function are:

  1. The cell containing the value which determines the colour of the light.
  2. The threshold to change from red to amber.
  3. The threshold to change from amber to green.

in this example, the Sheet2 looks like this:

Project 1 has a value of 64 and thus is amber.

Here’s a ZIP file with the .XLS and the images project-progress-dashboard, feel free to use it as you see fit.

Notes:

  1. The red and green images have an exclamation mark and a tick superimposed so that they are recognisable on a black-and-white printout.
  2. You must keep the 3 GIF files in the same directory as the spreadsheet.
  3. There is one bug. If you resize a cell containing a light such that the light is no longer contained within the cell’s boundaries, the light will not be deleted when its underlying value changes (you end up with an orphaned light). To fix this, there is a “Remove lights” button; clicking it will delete all images and pressing F9 will re-generate correctly.
May 272007
 

I was working for a consulting company in Rome when I met one of the most crass, self-imbued turds I have ever had the misfortune to cross. His disdain for his fellow colleagues was boundless and it was mutual, not one of us would have urinated on Jay, even if he burst into flames.

One morning, nursing a grappa-induced hangover, I decided the time was ripe to give Jay a dose of medecine. My colleagues enjoyed it, I hope you will too.

Jay,
I am leaving [company] today and would like to take this opportunity to settle some things with you.

I’m writing this slowly as I know you can’t read very fast, so pay attention.

You might have noticed that subsequent to [company]’s demise, things have been very trying for the team here in Rome. It certainly hasn’t even crossed your mind that a major portion of the grief we’ve been having is due to your crass, narcissic behaviour. For two months now you’ve been prancing around the corridors here like a bloated peacock on LSD, with hot air, vacuous promises and bovine excrement as your sole deliverables. How you could even imagine that [company] would actually take on a used-car salesman like yourself defies belief; it does however demonstrate clearly that your astonishing arrogance is matched only by your incredible stupidity. You have made an appalling image of our company and were you to have something other than dirt holding your ears apart you would be ashamed.

But I digress, I’m all for letting people fight their own battles, I have a personal axe to grind with you. You may recall that a couple of months back, when I resigned, I sent an email informing all concerned, in which I placed confidence in you to announce my resignation to the client at an opportune moment. A foolish mistake. Having made a complete botch-up of everything, you waited until we were re-negotiating the contract to make the announcement to the client at the worst possible moment. To worsen matters you did it behind everyone’s back and, spineless cockroach that you are, didn’t even admit to having done it. How you could try and sabotage so many of your own colleagues’ efforts to further your base little personal ends shows a despicable contempt. Fortunately the client saw through your miserable ploy, and we now all share a similar contempt for you. Truly, in the 25-odd years I’ve been working, you are the worst piece of scum that I’ve ever had the misfortune to encounter.

Had you a less faulty gene pool, you would have learnt that the world is a small place and people that you slight often reappear later in a superior position. I sincerely hope that this will occur and given the opportunity, rest assured that I will ream you dry with neither hesitation nor remorse.

Lest you perceive anything cowardly in sending this message, fear not, I have communication skills that you couldn’t imagine in your wildest dreams. You might like to focus your cramped, porcine imagination on figuring out the extent to which I have have spitefully blind-copied this email >:-|

To avoid any ambiguity note that I write this from a purely personal stand-point; don’t bother trying to associate this email with [company], I’m reachable at the address below.

As you may well imagine, I never got a reply.