Category Archives: Project Management

Guest Post: Rollover for Months and Years

Reader Bert van Zandbergen sent me a cool modification to the instruction file in How to: highlighting cells using the rollover technique in Excel to include years and months – not just rows and columns. He writes:

I am an enthousiast reader of the blog ‘Option Explicit’. I changed the formula (see below) and made a version for showing Years and Months. Now you can use this tool for Management Information. This fantastic tool, combined with Named Dynamic Ranges gives the ultimate solution for making awesome interactive dynamic Excel charts. As soon as possible I will show you an example.           

Bert van Zandbergen, Beekbergen  – The Netherlands 

You can download the file, here: Rollover_MI model version 1.xlsm

Thanks Bert!

How to: highlighting cells using the rollover technique in Excel

In my last post I used my patented rollover technique to create an effect similar to the one shown below:

Neat, huh? When you place your mouse over a cell, it changes color to show that you are selecting it. It’s a true “rollover”: no mouse-clicks required. In my last post, I show how that might be useful.

Wondering how I did it? Checkout this file:
Rollover Surprise.xlsm

Rollovers for Gantt Charts

Before we start, you might have noticed some renovations on this blog. Yeah, it was time for a change. I’m working this template out, so sorry if some of the formats seem off. Also, I have started a new facebook page. I’ll be keeping it updated with stories from my favorite blogs. Also, you can post questions and stuff for me (or others) to take a crack at. Plus, you’ll see that I’m just a normal average guy. So if you have a facebook, you should join. You really don’t have a good excuse why not! 🙂

DOOOOOOOOOOOOO IT!
Option Explicit VBA | Promote Your Page Too

***

In early last year, this humble blog had very little traffic. All of that changed in April of last year when I posted an exciting discovery: the Rollover technique. This technique was written about by the wonderful Purna of Chandoo.org. At the time, I was super excited about all of the possibilities of this technique. Now, just about a year later, I’m unsure whether anyone (besides me) has really ever used it. I even tried to incorporate it into my last project, but I found my clients were all too used to clicking on a cell rather than rolling over one. In the end I removed the rollover and replaced it with a click. Perhaps the rollover technique is destined to be a cool gimmick and not much more.

But I’m not giving up. Reader Bert van Zandbergen has been sending me many fine spreadsheets featuring this technique. His enthusiasm is encouraging. So I give you a rollover you can use for your Gantt Charts!

Download the fun, here:
Gantt Chart Surprise.xlsm

To see my intermediate table, scroll to the right of the dashboard. Sometime in the short future, I’ll post a how to make the highlighted cell stand out with a border. In the mean time, remember to go to Facebook and like this page. Seriously, what are you waiting for? 

A Dynamic Dashboard for Project Durations and Costs in Excel (part 2)

In the last post, A Dynamic Dashboard for Project Durations and Costs in Excel, we talked about using VLOOKUP as means to map references to their costs.  At the end of the post, I admitted that I didn’t really like the solution.  Specifically, I didn’t like how the user had to type in each reference manually and then go back and ensure their numbers were correct (for example, if execution is nine months long, the user would need to ensure they type the number 2, nine times).  In this post, we’ll talk about how to automate this process.  When we’re finished, you’ll only need to type the number 9 into a box and the rest will update automatically.  

The Advanced Method

Step 1: Use the REPT formula

The REPT() formula (short for “Repeat”) allows you to repeat a character a certain amount of times.  For example, if A1 has the formula =REPT(“A”, 6), then A1 will display the value “AAAAAA.”  Now think about how that might be useful here.  We could simply input the number “6” for IT Services in the Execution phase and Excel will generate a reference string “222222.”

Consider below:

In this case, =REPT(C$11$, C3) is telling Excel to repeat the value 1 (our reference to planning) a total of two times.  In other words,  =REPT(C$11$, C3) -> =REPT(“1”, 2) -> 11. (Remember, “11” is two 1’s, not eleven.)

Step 2: Create the reference string
Now concatenate each string reference (using the concatenation operator “&”) to form the entire reference string.

  
Step 3: Read from the reference string using the MID formula into your References Table
The MID formula allows you to take a string and read its characters starting from a specific position to a certain character length.  For example, =MID(“Jordan”,2,2) returns “or” since the first 2 tells Excel pull from the second character in “Jordan” and the second two tells Excel the amount of characters to return.  For our purposes, we’ll only need to return one month at a time.  And since our reference table incidentally uses numbers for months, we’ll reuse those numbers to tell the MID formula our desired character position.  When you supply a character position greater than the character length of the given text string, MID simply returns nothing.  

Above, we tell Excel to take the corresponding reference string for IT Services and use Month 1 to pull from position one in the reference string. Fill in the information going across, then down.  

Consider what we’ve done so far.  Now when you change the information in your Months in Step tablethe reference strings change, which makes updates to the Reference Table automatically!
  

Step 4: Convert references to numbers in your Values Table
We have a small problem. Our Reference Table may be displaying the correct information, but it’s actually displaying the information as text and not a number (this is because we used String formulas above, which work on and return text).  If we try to add the the numbers for the IT Services row in our Reference Table above, the result will actually be zero since, despite appearances, no numbers actually exist in the row.  Luckily, the fix is simple.  We’ll use the VALUE() formula to convert the strings back to numbers.       

Step 5: Ditch VLOOKUP.  We don’t need it.
VLOOKUP is best used to look up strings, not numeric values.  I’ve updated the look up table below.  Take note that each phase is actually intrinsically encoded: Planning is already in row 1; Execution in row 2; and Maturity in row 3.
In this case, we need only use the INDEX() formula.  The INDEX formula allows us to pull from a specific row and/or column in specified range.  For example, we could take the last column above and use the numbers in our Reference Table to identify the desired row.  Take a look below:
The Values Table uses in the References Table to pull from the first row of our lookup. 
Step 6: Use IFERROR for months with zero values
Another small problem.  The INDEX formula above is reading in blank values and returning errors!  You’ll need to tell Excel to return $0 for those months.  To do so, you’ll use the IFERROR formula.  IFERROR works by attempting to execute a given formula; if the formula returns an error, IFERROR will direct Excel to return another value instead of an error.
Take a look: 



Step 7: Sum the Values Table, then graph



Summary
Hopefully that wasn’t too bad.  I wanted to show how many different formulas can interact to create an automated dashboard.  Of course, you could tighten some formulas up.  For example, instead of using VALUES, you could simply use the “–“.  And, you could even do away with the IFERROR by retooling your lookup table.  I’ll leave that up to you.  


Something to try…
Use the reference table and conditional formatting to create a gannt chart!




We’re still missing some desired functionality.  Wouldn’t it be neat to be able to set the date when each project begins?  We’ll talking about that in Part 3.  Don’t miss the riveting, final conclusion!


Download the example file: Dynamic Duration Example.xlsx