46

I've been on a real rollover kick lately. I'm really trying to figure out if it can be useful. Earlier today I started making a Periodic Table of Elements using Excel. I employed the rollover technique to allow the user to gain information about an element simply by rolling over a cell. Well, for some reason, I couldn't stop there. So what was meant to be a small project ballooned into something larger. Unfortunately, my sticking to good coding practice didn't keep up with craving to do more. So what I present to you below isn't really a polished product. If you poke through the named ranges and the rollover indexes, you'll probably see that I add and subtract one to them somewhat randomly (a cheap trick - this is  due to my trying to reconcile the table copied from Wikipedia with my indices).

As you can see below, you can not only gain information about an element but you can also toggle on and off different element groupings.



If you want to "crack" the file, the first thing you'll need to do is reset the ScrollArea (Click on a cell. Go to the Developer tab, click Properties. Delete the reference in the ScrollArea box.). Then just unhide everything.

Good luck.
Periodic Table.xlsm

Update -
Reader Dario found an error in the spreadsheet (see the comments) - this is the result of some carelessness and cheap tricks on my part. An updated version will be released tonight. In the meantime however, you can still poke around the file :).

Another Update -
I've since fixed the bug described in the reader comments. If you find anything else, let me know!

Author: Unknown Labels: Advanced Excel, Data Visualizations, Dynamic Charts, Formulas, Interactive Dashboards, Rollovers, Spreadsheet Tricks, VBA

0

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 table, the 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 1 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

Author: Unknown Labels: Dashboards, Dynamic Charts, No VBA, Project Management

3

Make sure to checkout A Dynamic Dashboard for Project Durations and Costs in Excel (part 2) for the example file!

Let’s say you are the manager of a portfolio of projects at your company.  While each project under your aegis is different, they have a few things in common; specifically – and for the sake of our example – they all go through (or, are currently in) three different phases, and each phase has a different cost per month, but the cost doesn’t differ between each project.  In our example, the phases are:

Phase Name Cost of each stage per month
1. Planning $250/month
2. Execution $500/month
3. Maturity $125/month

The idea is to capture both the cost in any given month and the overall cost over the life of the project.  For example, you have an IT Upgrades project in your portfolio.  You think that you will spend two months in Planning, nine months in Execution, and one month in Maturity after which the project will be closed.  Thus, the overall cost of your project is expected to be,

Planning @ two months: 2 months * $250 $/month  = $500
Execution @ nine months: 9 months * $500 $/month = $4500
Maturity @ one month: 1 month * $125 $/month = $125
Planning + Execution + Maturity =  $500 + $4500 + $125 = $5125

The problem is that each project is different.  One project might spend more time in the first phase than the other.  Moreover, for some projects you have a good idea of how long they will stay in each phase, and for some you are a bit less sure.  Boy, you say, wouldn't it be nice to change how long a project stays in each phase to compare costs.  Wouldn't it be nice to see it on a chart?  Let's see what we can do.

Using a VLOOKUP might seem like an obvious choice for our solution.  Here's how to do it.   

The Dynamic VLOOKUP Method

Step 1: Create the VLOOKUP table
Since you know there are three projects, you could encode ids into a lookup table numbers as follows.

Step 2: Create a Reference Table
The Reference Table is a time-series table that holds the numerical references to your VLOOKUP table.  If IT Services has two months of Planning, then the Reference Table will show two 1's in the first two months, respectively.

Step 3: Create a Values Table to map each reference to its monthly cost using VLOOKUP
Now, you'll make another table that is essentially a mirror image of your Reference Table. This table, however, maps each reference to the correct cost.  So, for Month 1 in the Values Table, VLOOKUP will use the corresponding reference (1) in the Reference Table to indicate the project is in the Planning phase and the associated cost is $250.

Step 4: Sum the Values Table, then graph

Summary
Our dynamic VLOOKUP() method works: the manager (that's us) can replace the values in the Reference Table to update how long a project stays in each phase.  We would simply repeat numbers in the Reference Table in the amount of months a project is in a particular phase.  Our Values Table automatically updates based on each change.



So here's the thing.  I don't like this method at all.  For one, we need a VLOOKUP in for every month, for every program.  Our example only displayed a few projects in a small time frame, but the real world might have many projects over many years!  That's a real problem because VLOOKUPs can become computationally expensive as our spreadsheet grows.

And there's another problem, too.  The manager – that's us, remember – must hand-jam the references in for every month. What if there are many years to account for?  We could Copy/Paste to make life easier, but this method is so very, very error-prone.  There should be a way for us to simply enter the months a project is in a given phase and have Excel automatically generate all the required reference information.

Let's talk about how to do that in the next post.

If you're having trouble with the work above, take some time to see if you can recreate your own version in Excel.  Even if you have followed everything so far, consider the VLOOKUP method and the importance of using numeric references.  They will play a very key role in part 2. 


-----


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

Author: Unknown Labels: Dashboards, Dynamic Charts, No VBA, Project Management

3

This is a long overdue article, my apologies.

The basis of the article surrounds a question in the comments section of an article on Chandoo.org (comment #75) written about the hyperlink() method I discovered and wrote about here. In Chandoo’s example, when a user rolls over different “hotspots” representing different datasets, the corresponding data comes into view on a graph below. In other words, the rollover technique tells Excel to change the data currently being fed into the chart. One of the respondents to his post attempted an associated technique that would create a dynamic hyperlink to the dataset. The goal, essentially, was to create an “edit” button that could bring the user to the dataset in view.

The problem he (and I) ran into was that whenever we updated the link in the Hyperlink formula, the hyperlink's friendly name, “Edit,” would be replaced by the address of the actual link. This resulted in an Edit hyperlink that read something like “Sheet!A1” instead of “Edit Sales.” While the link worked, the result was ugly. We needed to try something different.

So here's my solution. It’s nothing really fancy, but it does require you to keep track of two components: (1) the tab that holds your associated data; and (2) the beginning cell of your associated data. There are, indeed, many ways to keep track of this information, but some planning and organization helps out a lot. One of the easiest organization techniques is to have your data in the same cell for each tab. If your “Sales” data begins in cell B1, then any other data you'd like to use for this technique should also start in B1. Moreover, if you're interested in information from your sales tab, name your tab “Sales” (and your other tabs “expenses,” “income,” etc.).  Similarly, if you are pulling from different databases, tab one could be Database1, tab two Databse2, etc. Alas, not all data can be organized in this manner, but you get the picture. Such organization makes a significant difference when implementing this technique and my example below assumes you have data from tabs Database1, Database2, and Database3.

A Quick Refresher

The dashboard above works the same way as the example used in Chandoos’ post. The cell’s with the down arrows are the rollover hotspots and they contain the required hyperlink formulas.

For example, when the user places his or her mouse of cell B5, C5, or D5 the following code is executed.


Public Function ChangeSelection(i As Integer)
     Range("valSelOption") = i
End Function



Hopefully this is all review for you. If not – or, for a quick refresher – read these:
1. Rollover b8 ov1
2. Interactive Dashboard in Excel using Hyperlinks

On to the Good Stuff

So now we need a button that, when clicked, will take us to the location where the data resides. As you might have noticed from my example above, I placed a textbox on my dashboard with the label “Edit.” This is my edit “hyperlink.” But instead of actually “hyperlinking” to the data, I’ll instead tell Excel to take me to the data with a macro. In my module I’ve written the following code and have assigned it to the textbox.


Public Sub GoToData()


Dim wsCurrent As Excel.Worksheet
Set wsCurrent = Worksheets("Database" & Range("valSelOption"))
wsCurrent.Activate
wsCurrent.Range("B2").Activate

End Sub


Here’s what’s going on. In the earlier code section – my ChangeSelection function – I told excel to change the named range, valSelOption, to an index representing the location where my data resides. But I’ve also named my tabs Database1, Database2, and Database3. Since I placed my data starting in range B2 for each tab, I can use valSelOption to direct Excel to the correct tab. In the above code, I create a worksheet object and assign it to the correct worksheet by concatenating “Database” with valSelOption. In other words, if I valSelOption is 1, then the database we want will be Database1...and so forth.

Also, notice that my code first activates the tab we want before activating the range within it.  This is an important step that must come first or you will surely get an error.

---

So what if your data isn't organized in an easy, uniform way? Well, in my example, you might change cells B3 to D3 to the name of your tabs. And in your ChangeSelection() function you would have a string argument instead of an integer. That could help your program identify the tab housing your data. Again there are different ways to do this.

But if you find that your data is all over the place – consider reorganizing!

-

(I’ll have the file up with this example soon.)

Author: Unknown Labels: Dashboards, Dynamic Charts, Excel Tricks, VBA

362

"Rollover B8 OV1" (like Rollover Beethoven...? get it?)

Update 17 July 2011: This blog entry was featured in Chandoo's latest post. So if you're coming here from his site -- welcome!

original post:

This post was inspired by Chandoo's post on hyperlinks in Excel. If you haven't checked his website out yet - you should. It's excellent.

In this post, I suggest a method for creating a "Rollover" effect for your mouse on an Excel spreadsheet. Rollover techniques (also called "Mouseovers") are useful to (1) display quick information to the user; (2) to execute a function when a user puts their mouse over a specific region; and (3) to make neat graphical effects.

For item 1, my technique isn't really necessary. If you want to display brief information to the user when he or she rolls over a cell, the best way to do this is to insert a comment.

But comments are pretty limited. You can only write so much information in a comment before it becomes too cluttered to be useful. Moreover, comments are static. What if you want the information concerning the "target" cell (that is, the cell to be rolled over) to change based on different items in your spreadsheet? We can use the in-cell Hyperlink() formula to accomplish this.

The Hyperlink() formula looks like this:

=Hyperlink(location, [Friendly Text])
location - Here you write the intended address of your hyperlink. This can be a webpage (i.e. "http://www.google.com") or a file on your computer.
[Friendly Text] - This is an optional field that provides a caption to your hyperlink. If you leave it blank, it will simply display the address in the previous parameter.

To begin our example, you'll need to create a User Defined Function (UDF) to be executed when the rollover event is initiated. So go into the Visual Basic Environment, create or find a free module and create a UDF that looks something like this.

Public Function MyMouseOverEvent()

Sheet1.Range("A1").Value = "Event Fired!"

End Function

I can understand if you're puzzled at this point. UDFs are not really allowed to change values in other cells. However, since we'll be calling this function from within the Hyperlink() formula, you'll see this limitation no longer exists.

So now go to Sheet1 and click on cell A2. In that cell, type in the following formula:

=HYPERLINK(MyMouseOverEvent(),"Click here")

Upon hitting Enter, you should get a #VALUE! error, but ignore it for a second. Roll your mouse over cell A2 - and viola! - Cell A1 should now say "Event Fired!"

But what about that pesky #VALUE! error? Remember the limitation that UDFs can't change other cells? Well, even though Excel let's you change a value from within your UDF, it still recognizes that it wasn't supposed to! To get rid of that error is pretty simple. Just rewrite the function in your cell as follows:

=IFERROR(HYPERLINK(MyMouseOverEvent(),"Click here"), "Click here")

And that's it. If you like this technique and find some cool uses for it, drop me an email and show me.

Here's some graphical fun I created using this technique. Putting the mouse inside the black rectangle creates a fading snake-like pattern based on your mouse movements. Check out the screen capture:

Make sure to check out my latest (and greatest) example of this technique, here:

Periodic Table of Elements in Excel: Rollover Edition!

Author: Unknown Labels: Dashboards, Dynamic Charts, Excel Development, Interactive Dashboards, Spreadsheet Tricks, VBA