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

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

Reader, Bert van Zandbergen, from Beekbergen, The Netherlands (Holland) sent me his own cool creation based on the Rollover technique I created. I asked if I could post his work to this blog to which he graciously agreed.

Below is his write up. Some of the Excel functions below are in Dutch (which I think is really cool). If you're confused, download the file first - your version of Excel will show the functions in your language of choice. And, of course, if this technique is new to you, read the the tutorial on how to do Excel rollovers. 

***

Dynamic Funnel Chart

by

Bert van Zandbergen

The Netherlands

Based on this file: 

presentatietechniek-25-pipeline-2012.xlsm

Figure 1

Start with the 4 columns. The dummy had to be 100 or more for space to unfilled dummy bars. Delete the lines. Now you can change the value from “100” to “1”. Further information is visible in Figure 1.

Hyperlink:
Read also the information and explanation on the website of Chandoo.org
http://chandoo.org/wp/2011/07/20/interactive-dashboard-using-hyperlinks/ 

Figure 2

Place a hyperlinks in the cells of the hotspot – see above. 

Figure 3
Formula: Define the hotspot with the name: “ valSelOption” 

Figure 4

Go to VBA and insert a module. For more information about the instruction – see on this website, the Chandoo website and the module above. 

Figure 5 -- Chart with hotspot

The hotspot is based on 10 columns combined with 42 rows. – see figure 5/6. The hotspots are linked by hyperlinks with corresponding cells in columns AI:AR.  For a special effect and an easy crossover the values are placed in a diagonal figure

See: Figure 5/6  

Figure 5 -- The "hotspot"

Above the hotspots and the linked cells. Special formed to make an easy crossover.

***

A big thank you to Bert - hopefully there will be more contributions in the future!

Do you have something interesting to share? Send me an email or drop me a line on LinkedIn. 

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