Category Archives: Dashboards

Directly link Excel form controls to backend data with dynamic references

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.

Advertisements

Details on Demand: Bring up details on a graph with Excel rollovers

So, as usual, I’ve been experimenting more with the rollover technique. Last night, I started experimenting with adding the capability to rollover a point on a chart (instead of just a worksheet cell) to see if I could have a label appear – or just some event fire – upon the mouse entering a chart’s datapoint’s “hotspot.” The goal was to improve upon previous attempts to provide details-on-demand for charts. Such attempts usually required that you create a reference to a chart object and use chart sheets. Personally, I don’t like chart sheets.

In the end, I moved beyond just firing an event when your mouse hovers over a data point; instead, I created the functionality to select a rectangle of data points to display information about them. See:

Because I’m now writing a book, I don’t really have time to go through what I did at length. But here’s a short summary.

1. I first made every cell into a square of the same size, which you can learn how to do by reading my “Most Squares Method.”

2. With each cell the same size, I now had a grid that I could turn into”hot spots” for the mouse rollover. I laid out my chart to use 36 (0 – 35) squares horizontally and 17 (0 – 16) vertically. There was really no good reason for why I picked these numbers; my choice was pretty arbitrary. However, for this method, the more squares you use, the more hotspots you create; thus, more squares means more precision.

3. I then created a mouse rollover technique to capture the row and column numbers set above. Using these numbers, I mapped them on to the charts grid (so for row two, I would do 2/17 * y-axis value to get an approximate mapping). With these mapping I could approximate where the hotspots would light up certain points that were within them

4. I didn’t want the user to be able to click on the graph and change its values. So I took a shape and placed it on top of the graph and assigned it to fire a macro on click. You can’t see the shape because the fill color is fully transparent. Reread that last sentence and note that I did not say I used “no fill color.” When you assign a macro to a shape, if you select “no fill,” Excel lets you select anything that is contained by the shape as if it’s not there. That would mean the user would be able to select the graph, which is exactly what I didn’t want. For the shape above, I simply set its transparency to 100%.

5. Finally, to make a long story short, the shape when clicked fires the macro that allows the user to draw the rectangle.

I know right now my work isn’t perfect (some data points aren’t selectable for example depending upon where you start the rectangle), but I like what I have now and don’t have much more time to work on it. I’ll leave it to you to put it to good use.

Let me know if you make something cool.

Download File
Details on Demand Rollover.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? 

Easy One-Way Sensitivity Analysis on Weighted Sum Models in Excel (Part 2)

Wow, it’s been a while since my last article. I’ve actually been pretty busy the last few weeks putting the finishing touches on an Excel deliverable for one of my company’s clients. We delivered the product about a week ago and things have just now started to slow. So now it’s back to blogging.

If you’ve been following so far, we left off with Easy One-Way Sensitivity Analysis on Weighted Sum Models in Excel (Part 1). In that article, I describe a method for automating one-way sensitivity analysis, and, in Part 1, we construct the mechanism that drives the automation. However, where we left off, our sheet was a bit ugly. Sure, the mechanism worked, but it wasn’t a dashboard. We’ll talk about making a dashboard in this article, Easy One-Way Sensitivity Analysis on Weighted Sum Models in Excel (Part 2). If you’re scratching your head at this point (“what the heck is sensitivity analysis?”), go ahead and start with Part 1. There, I give a brief introduction to the mathematics and rational behind sensitivity analysis (and the weighted-sum model) before going into the Excel stuff. Also, these tutorials are a bit on the long side, so you may just want to do the analog thing and print them out instead of following along on here. 

I’ve been scratching my head as to how to segue from my last article to this one. I’ve decided to do what good chefs do and start with something already prepared. So go ahead and download this file: Healthcare Sensitivity Analysis Example.xlsx. You’re welcome, of course, to recreate the layout of that file, but I’ve done the heavy lifting for you. The core difference between this file and the one you created in the last blog post is that I’ve added many more countries (with fake data). Let’s get the lay of the land of this new file.

If you start with the Data tab you’ll something similar to our last blog post.

The table on the left isn’t all that different from the last file. If you play with the scrollbars, you’ll see that they work exactly as they did before. In the table to the right, each metric has more information broken out than in our last spreadsheet, but the mechanism we created before is exactly the same here (notwithstanding the extra information). Note, too, that the final weighted scores have been moved the front (that is, to the first column on the left) of the final table. You’ll see why at the end of this tutorial.

(The reason more info is broken out is because that info is used in the Healthcare Analysis dashboard in the Fun Downloads section. We won’t use those extra columns here, but if you get the hang of this stuff, consider creating a dropdown to individual weighted scores using that extra information. I might write about how to do that in a Part 3.)

Now click the Example tab. You should see the barebones of our dashboard. If you scroll all the way down (or zoom out) you should see another table below the dashboard called VLookup Table.

Dashboard 

Intermediate Table

This table is an important, if unnecessary, intermediate step. I say unnecessary because you could go without out, but I wouldn’t recommend it; the table makes life much easier. So, this is my advice: whenever you make a dashboard, you should have an intermediate table off-screen that summarizes most of the selection and number crunching for you. For the computer science and java geeks out there, think of this table as part of the model-view-controller framework. The dashboard is our view, or “reporting” layer – it reports data to the users, but it doesn’t allow them to make specific underlying changes to it (they can only change how the data is shown to them). This intermediate table acts kind of like a controller. It handles input stuff like scrolling, and it’s usually only for the Excel developer and not for the client or front-end user (that’s why it’s off-screen). Finally, the underlying data on the backend is our model. I admit that’s a pretty rough explanation, but it serves this very fundamental point to dashboard creation: you should separate the reporting, event handling, and underlying data areas on your spreadsheet. This separation mitigates damage caused by underlying and user-created errors and separates the logic of your work into distinct modules. In other words: it makes life easier.

Let’s go.

Step 1: Link the percentages from your data to your dashboard. 


Start by selecting cell I3 on the Example tab and link it to cell E4 on the Data tab, which holds its corresponding weight. Then go across the boxes on your Example tab and ensure that each cell is linked to its correct proportion on the data tab. Your numbers might be different from mine above, but if you do everything correctly, your mapping should look like this:

Health Level                → Data!E4
Responsiveness              → Data!E5
Financial Fairness          → Data!E6
Health Distribution         → Data!E7
Response Distribution       → Data!E8

It’s always good to double-check to make sure your references are correct. Check twice, reference once.

Step 2: Copy the scrollbars to your dashboard. Resize accordingly.

Remember those scrollbars from the last blog post? They’ll come in handy here. Go to the Data tab. While holding down Ctrl, select all five scroll bars by clicking each one individually. Press Ctrl+C to copy them. Next, go to your dashboard on the Example tab. Press Ctrl+V to paste to your dashboard.

You’ll now need to adjust the size of each scroll bar – then position it next to each number as shown below.
 

Pro Tip: You can make your life easier by selecting the first scrollbar on the left (select it with a Ctrl+Left-Click) on your dashboard. Move the control on top of, or near, the Health Level box and adjust it to the desired height. Use the arrow keys on your keyboard to fine tune its placement (you may need to adjust the height one more time). When you like the scrollbar’s size and position, right-click and select Format Control. Select the Size tab. Take note of its current Height and Width (write it down if you need). Click OK.

Now select all of the remaining scrollbars using the Ctrl+Left-Click as you did in Step 1. Right-click any one of the selected scrollbars, then select Format Control. In the Height and Width boxes enter the information you just noted. Click OK. Each scroll bar is now the right size. Move each scrollbar next to its associated number. Use the arrow keys to fine tune if you’re neurotic (like me!).

Lookin’ good….

Step 3: Create the scrolling indices to show each country.

On the Example tab, scroll all the way down to the VLookup table. In cell F32 of the Example tab you should see only the value “1” alone, by itself. In the cell below the 1 (F33), type =F32 + 1.

Hit Enter. Now drag cell F33 down until you reach the value of 15. Next, go to the Developer tab and insert another From Control scroll bar to the left of the column of numbers we just created. Just like in Step 1, we’ll link this scroll bar to a specific cell on the sheet; in this case, we’ll link it to the cell that held the 1, cell F32. So right-click the scroll bar, then select Format Control. Click the Control tab. In the cell-link box, select (or type) F32. Click OK.

Press the up and down arrows on the scroll bar to see the indices change.

Step 4: Use the indices to pull the final weight values from the Data tab.

Here, we’ll use the scrolling indices to pull information from the Data tab. We can use the Large() formula to ensure the data we pull is sorted. Use the scrollbar to scroll up so that there is only a 1 in F32. (If you see a zero in F32, you’ve scrolled too far. Scroll up one.) Now, in the cell to the right of it, type “=Large(“. Now select the entire column of Final Weights from your data tab, Data!G4:G53.

Hit F4 to make it an absolute reference. Type a comma to go to the next parameter. Now, go back on your Example tab, select the 1 (cell F32) to the left. Your formula for F3 should look like this:

=LARGE(Data!$G$4:$G$53,Example!F32)

What we’ve told Excel to do is pull the greatest value from the set of Final Weights. Hit Enter.

If we drag that formula all the way down, we are then telling Excel to pull the second largest value, third largest value, and so forth. Thus, the resulting values in the Largest Values column are always nth largest value, where n is the number in the cell to the left. That’s how we make an automated sorted list. 

Step 5: Fix the Scrollbar minimum and maximum values.

If you play with the scroll bar, you’ll see that scrolling all the way up or scrolling up the way down results in #NUM errors. This happens because we only have 50 countries to choose from. Indices less than one or greater than fifty are outside the bounds of our set. So right-click the scrollbar, select Format Control. Select the Control tab.

Right away, we know that the Minimum value should be 1. But if we have 50 countries in our set, what do you think the maximum value should be? Hint: it’s not 50. Remember, we’re only changing the value in cell F32, the rest of the values are calculated for us on the spreadsheet. So the maximum value should actually be 36. Why? Because while we have 15 different indices showing, one of them is the cell link and the other 14 are calculated on the spreadsheet. 50 – 14 = 36.

In Sum: 

Minimum Value: 1
Maximum Value: 36 

Click OK.

If you play with the scrollbar, you’ll see that it keeps the indices within the correct bounds.

Step 6: Use VLookup to take largest values to its corresponding information.

In the first cell to the right of the Largest Values column (cell H32), we’ll use the Weight Value to the left as a lookup value. Now, do you see why I moved the final weighted scores all the way to the left on the ata tab? It’s a lookup column now! So, in Cell H32, you’ll have something like the formula shown below. But rather than typing what I have, try to recreate the formula yourself. Then double-check to ensure that our work agrees. 

=VLOOKUP(G32,Data!$G$4:$R$53,2,FALSE)

(1) G32 is the weighted score;
(2) Data!$G$4:$R$53 is the entire table from the Data tab;
(3) 2 tells us to pull from the second column, that’s the name of the country we’re interested in; and,
(4) FALSE ensures an exact match.

But wait, the VLookup Table on the example tab has a column for each metric, and we’ve only filled in info for one column. We’re not just interested in the second column from the table on the data tab. We’re actually interested in columns 2, 4, 6, 8, 10, and 12 as demarcated in red below.


We can actually pull all that info out with only one VLookup. On your data tab, select H32. Now, rewrite the VLookup formula as follows:

=VLOOKUP(G32,Data!$G$4:$R$53,{2,4,6,8,10,12},FALSE) 

Note we have changed the formula from looking up only column 2 to looking up a set of columns. Ensure that you use the curly braces to surround your set of columns as shown above. Now drag the selected cell to the right until you reach column M, the end of the Vlookup table. With H32:M32 still selected, click into the formula bar, then press Ctrl+Shift+Enter. Viola! We use the Ctrl+Shift+Enter here because the VLookup is returning an array of numbers and not just one number. Now drag the selected row all the way down to fill the table. If you’ve done everything correct so far, your table should look like mine.

Use the scrollbar to see the values change.

Step 7: Map the values from the intermediate table to the dashboard.

First, select the scrollbar to the left of your VLookup Table. Press Ctrl+C to copy. Scroll all the way up to the dashboard. Now paste. Move this new scrollbar to a location on your screen that makes sense. You could put it between columns E and G. Or, you can put on the right, as I have, to the right of column O.

Looking at your dashboard, select cell G6. Set G6 to reference the nation at the top of the list in the VLookup table. So, G6 should have “=H32” as its formula. Now drag G6 down 14 rows. If you see one of your rows start showing a value of “0,” you’ve dragged too far. Now, while looking at your dashboard, select cell O6. Map this cell reference the first weighted score on your VLookup table. O6, then, should have the value “=G32.” Drag down. The preset bar charts should show up automatically.

Now play with every scrollbar on the dashboard.

***

This is a good stopping point for Part 2. I might be working on a Part 3 as time allows. If I end up writing a Part 3, we’ll talk about how to make those bar charts. And, we’ll talk about how to build another graph that shows how each country performed in just one metric. Lastly, we’ll talk about how to populate the top and bottom rankings.

If you’re really curious about the incell bar charts, you can see a good discussion on Chandoo’s site,
How to Visualize Survey Results using Incell Panel Charts.

Questions? Feel free to ask.