Conditional Formatting

This picture is actually a zoomed out snapshot of an Excel file that shows our tracking of the rollout of software to the various sites. We have so many tasks to be applied to each site and there are only 4 or 5 of us that using Basecamp wasn’t working for us. It just ended up being an intimidating long list of to-do’s and we couldn’t grapple with the situation. So, I’m trying to use Excel as our tracking tool in a visual way. So far, the response has been positive. There are six groups or tracks of sites that are being migrated to the new software. Each group starts the migration on a different date but we could have concurrent tracks going on at the same time but at a different stage in the migration lifecycle, which makes it difficult to remember what needs to be done when. There are probably 2 to 3 more tracks but they are further out in the future. The green cells are the tasks that have been completed, the red cells are those that should have been completed as of today but haven’t and the yellow are the tasks to be done in the future. So you can see that in most of the tracks we are already behind. This color system was done by using conditional formatting. I learned this week that if you are not careful in the set up, you could copy these cells and the conditional format formulas would end up being in error. My earlier post mentioned BOA’s $4 billion error; here’s a tiny error that could show up and you would never know. Furthermore, a person could copy a note from one cell to another, thus copying the conditional formatting, but the formulas would copy incorrectly. And you would never know. [divider]What’s in the Conditional Formatting[/divider] I first set the cells in yellow and then I add conditions to check the task’s date against today’s date and to check for the completion mark before assigning an appropriate color. I have one condition to test for red: the task is NOT denoted as completed and the task’s date has already been passed. I have two others to test the green condition: the task has been marked as completed with a “c” or has been marked as not applicable with a “NA”. I used to format a block of cells (multiple rows and columns) to set the conditions but after seeing what happened this week, I’m going to stick with formatting row by row (or column by column). I think if you set up the conditions for the first and only time, you can conditional format a block of cells. But if you are going to copy a block of cells and “reuse” the conditions, it may not work so well.