When building a web analytics dashboard, adding interactivity is often required to make the application truly useful. In this example, you will see how we can add a web profile selector to our web dashboard with very little effort.

In our previous examples, we created a daily web dashboard with a couple of front-page views into our web site activity, but it was ‘hardcoded’ to a specific account and web profile. If we look at the ProcessedGoogleData_actions tab, we can see the Nextanalytics script that was automatically generated, and it is easy to see where the account, password, and profile number are positioned in the first line.

The script commands are simple text strings, and we can replace them with simple formula equivalents. This allows us to change the various components into cell references. We do have to exercise a bit of caution, though, because of how Excel handles cells that contain double quote symbols. This is a really long string, so let’s break it down to understand it better.

There are a number of ways to turn this into a formula, in this example I will use a series of concatenated cell references to avoid the confusion of handling double quotes. The formula will look like: = B4 & B5 & B6 & B7 & B8 & B9 & B10 & B11 & B12, and in the cells we position the needed elements. The script formula can now be copied to the ProcessedGoogleData_actions sheet in place of the original text.

So far, so good, but no one wants to enter their profile number manually. They would rather pick their descriptions from a dropdown list, and Excel offers a simple solution – Forms controls. In preparation, we setup a couple of lists – the profiles descriptions and their associated numbers. Then we add a Combo Box to the dashboard worksheet (Excel 2003: click Combo Box on the Forms toolbar, Excel 2007: click the Developer tab and then click Combo Box in the Forms Controls section).

Right-click the control and then select Format Control. For the Input Range, use the list of profile names, and for the cell link, select a cell near the list. This cell will contain the index of the item selected in the combo box.  Click OK to close the dialog. You can try out the combo box and see how the index value in the worksheet changes according to your selection.

To complete the script construction, we need to put the associated profile number into the script command formula, and this can be done with an Excel INDEX() function

Now when we pick a profile from the dropdown, the script command will change to reference the associated profile number. All we need to do is Refresh the workbook. If you have saved the Nextanalytics information as VBA function calls, you can automate this part as well. Right-click the combo box and select Assign Macro... and then pick Sample Button_Click. Now when you select a value, Nextanalytics will run the updated script commands and refresh the dashboard for the selected web profile.

And there it is — in just a few minutes using simple Excel functionality we have added interactivity to our Daily Web Dashboard (Download the workbook here), and we turned our prototype into a template that can be easily used with other accounts and profile combinations.