{"id":26,"date":"2008-10-16T09:00:53","date_gmt":"2008-10-16T07:00:53","guid":{"rendered":"http:\/\/www.calvert.ch\/maurice\/?p=26"},"modified":"2011-05-10T22:16:09","modified_gmt":"2011-05-10T20:16:09","slug":"project-management-dashboard-excel-with-traffic-lights","status":"publish","type":"post","link":"https:\/\/www.calvert.ch\/maurice\/2008\/10\/16\/project-management-dashboard-excel-with-traffic-lights\/","title":{"rendered":"Project Management Dashboard – Excel with traffic lights"},"content":{"rendered":"

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:
\n\"\"<\/a><\/p>\n

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 :
\n =trafficlight(Sheet2!B4,Sheet2!B$3,Sheet2!C$3)
\n<\/span>and the rest of the lights are created by dragging this formula right and down.<\/p>\n

The parameters to the TrafficLight function are:<\/p>\n

    \n
  1. The cell containing the value which determines the colour of the light.<\/li>\n
  2. The threshold to change from red to amber.<\/li>\n
  3. The threshold to change from amber to green.<\/li>\n<\/ol>\n

    in this example, the Sheet2 looks like this:
    \n    \"\"<\/a><\/p>\n

    <\/a><\/p>\n

    Project 1 has a value of 64 and thus is amber.<\/p>\n

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

    Notes:<\/p>\n

      \n
    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.<\/li>\n
    2. You must keep the 3 GIF files in the same directory as the spreadsheet.<\/li>\n
    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.<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"

      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, […]<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,11,13],"tags":[19,37],"class_list":["post-26","post","type-post","status-publish","format-standard","hentry","category-finance","category-technology","category-work","tag-excel","tag-traffic-lights","category-3-id","category-11-id","category-13-id","post-seq-1","post-parity-odd","meta-position-corners","fix"],"_links":{"self":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts\/26","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/comments?post=26"}],"version-history":[{"count":2,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts\/26\/revisions"}],"predecessor-version":[{"id":581,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts\/26\/revisions\/581"}],"wp:attachment":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/media?parent=26"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/categories?post=26"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/tags?post=26"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}