0

Form controls are great for reporting information about groups of items, like a list of programs or accounts. They are often used on Excel dashboards and reports that demand interactive capabilities. One such type of capability provides the user with a list of items to choose from. When the user makes a selection, a macro is executed that populates a table holding referenced values. Those values are linked to a series of form controls on the frontend. This interactivity is displayed below:

Step 1

Step 2

Step 3

There is a final step, which I haven't included. The user would make changes to the project under the Options table. They would press a "Save" button and their changes would be copied from the Linked Values table back onto the backend data in the column corresponding to the selected project using VBA.

The No-VBA way

There's nothing wrong with this method in and of itself, but I want to propose a method that requires no VBA. The advantage of this new method is that it links directly to the data itself and bypasses the need for the Linked Values table. We can do this by allowing the form controls to take advantage of dynamic references.

Typically, form controls can only do direct, absolute references. You cannot, for example, use VLOOKUP or INDEX within the source field of a form control. However, you can use a named ranges.

Let's do it!

First, we give that 'index' field above a named. How about selection? Next, we create four named ranges to correspond to the form control checkboxes. Stage_1 to Stage_4 are those new named ranges.

As you can see from the picture, I use the fourth row to connect to checkbox Stage 4 and the selection value to inform Excel to pull from the fourth column in the backend data (which is Project 4, if you recall).

Finally, I can simply link these named ranges to their associated checkboxes:

Using this method, changes to the checkbox automatically change the backend data. There is no intermediate table required -- like the Linked Values table above -- to interface between the frontend and the backend.


That's all for now - have a happy and health holiday season!

Update 25 December:
Make sure to see the download file - Direct Links.xlsm.

Author: Unknown Labels: Interactive Dashboards, No VBA, Spreadsheet Tricks

1

Ok, so I know I promised no more updates, but this one is too cool. Basically, I combined Andy Pope's 3d rotation tutorial with the rollover method. The result is a free-floating cube which you can rotate by passing your mouse over it. Here's a choppy-ish video of what's going on. I promise however that it run smoothly on your computer. The choppiness is a result of the video's frame rate.

The mechanism is similar the the election map and the details-on-demand graphs: the chart is placed on top of the hot spots to get the mouse's coordinates. In this case, the rollover pulls in the mouse's location and initiates a timer. The timer adjusts the rotation angles based on the mouse's location. Additionally, some fancy calculations in the timer process procedure give the rotation a "slowing down" effect. I urge you to open on the Visual Basic Editor and do some dissection, if you desire. Remember though, the Timer API is a bit finicky. Make sure to save often in case your Excel crashes while in the middle of your investigations.

The download file: Rotate Cube Rollover.xlsm

Have fun.

Author: Unknown Labels: Cube, Rollovers

1

These last few months have been great to this humble blog - and to me, your humble blogger. We've received a lot of traffic, especially for posts concerning the rollover method (40k pageviews overall as of today!). Also, I got married in October! 

In pains me to tell you this, but I'm taking a break until next year (you probably already noticed my absence). In August, I signed on with Apress publishing to write a book about developing Excel applications and dashboards. I'll be honest with you - I'm woefully behind schedule, and I really need to catch up. Writing this book has been a dream of mine. So I really need to focus on it. 

In my last post, I said a tutorial on how I created the Election map would be forthcoming. At this rate, I'm not sure when that will be. Thankfully, Robert Mundigl of ClearlyAndSimply.com has written about the very same technique the Election map uses in his phenomenal article, Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard. I can't really thank Robert enough for this. His article is a pleasure to read (as are his other articles) with explanations that are thorough and thoughtful; certainly, they surpass anything I've ever written on the subject. So, if you haven't read Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard, please make your way there as soon as possible. 

Another person I need to mention is Bert van Zandbergen who has been my silent partner-in-crime concerning investigations into the rollover method. He has toyed and tampered with the rollover method producing spreadsheets that are different but always interesting. I mean, just take a look at these:

• Dynamic Circular Arrow
• Dynamic Half-Filled Circles

You can contact Bert at: klvzndbrgn (at) gmail.

Finally, the people I really need to thank are you, my readers. This blog wouldn't be what it is without you. 

I won't be gone forever. My facebook and twitter will be alive again soon. Make sure to check them out.

Oh yeah, and a happy holidays to everyone :)

Author: Unknown

11

Alright, here's another map-based rollover. The instructions are pretty simple: click on a state to toggle through its party selection. Click on one of the three selections above to change scenarios.

In the next month or so, I'll be putting together a tutorial on this. But in the meantime, have some fun. Download. Ask questions if you need.

For more on these types of maps, checkout:
Choropleth Maps with Excel

Download here:
Election Map.xlsm

How about another screenshot?

Author: Unknown Labels: Excel Rollovers, Rollovers

0

Chandoo (of Chandoo.org) and Daniel Ferry (of ExcelHero.com) are kicking-off their new Excel School and Excel Hero Academy. If you really want to learn Excel—and I mean learn Excel—I recommend their new courses.

It’s true; I’ve never taken a course from either of them. However, much of what you see on this website is a direct result of their work, their tutorials, their fearless trailblazing into Excel’s unknown. All of my work (not just the stuff on this blog, but my professional work too) owes a great debt to both Chandoo.org and ExcelHero.com. I can only imagine the great work they do online is expanded in their classes. In all honesty, I would sign up too if I wasn’t so pressed for time in the short term. But if you have an interest in learning Excel and taking it to the next level, I can’t think of two better people to teach you how. Believe me.

I’m not a paid spokesperson. I won’t receive anything for referring you. In fact, unless Chandoo or Daniel views this blog article (here’s hoping I’m important enough for them to follow!), they won’t even know I’ve made a recommendation. But I believe that strongly in their work.

So sign up for the Excel School & Excel Hero Academy Bundle. I think it’s well worth the money.

Author: Unknown