September 17, 2014

Automating dash boards using list box form control with choose function

How to automate creation of dash boards using a list box form control with the choose function.

  • Input data for let’s say, total revenues, total expenses and then calculate the net income by subtracting the total expenses from the total revenues for different regions
  • Using this data as the base, the list box references and the choose function create a new data range as shown in the video
  • Now select, let’s say, the revenues data and the corresponding month headers, click on insert, click on the drop down arrow next to ‘insert column chart’ and select clustered column chart  to insert the chart on your worksheet
  • In a similar fashion insert clustered column charts for expenses and net income
  • Also select the data of revenues, expenses and net income along with the month headers and create a single clustered column chart
  • Now the data that we display on all the charts is linked indirectly to the list-box by performing the following steps:
  1. Click on the developer tab
  2. Click on insert in the controls command group
  3. From the form controls select the list box control
  4. Click and drag on the Excel worksheet to place an appropriate sized list box
  5. Right click the mouse button and select ‘format control…’ from the menu
  6. In the new ‘Format Control’ wind input the input range by clicking on the collapse button and then selecting the cells S1:S5
  7. Click again on the collapse button to expand the Format control window
  8. Click inside the ‘cell link’ text box, click on the collapse button and select the cell T2 on your worksheet. Now the cell T2 is linked to the input range. When you select an item from the list box a value in T2 will be displayed. For example, if you select the item ‘All Regions’ in the list box, then 1 will be displayed in cellT2
  9. The value in cell T2 is also used by the CHOOSE function to create the new data range from our raw data. Therefore, when you select an item from the list box, the value of cell T2 changes. This value is used by the choose function, as already mentioned, to create a new set of data and this data is used to create the revenue, expense and net income charts automatically. In fact, a combo chart of revenue, expenses and net income is also created automatically
  10. Now the user can quickly view the key parameters of any single single region or all the regions quickly and easily and derive his conclusions

View the Excel training video to see how the dash boards are created automatically using a list box:


Watch this training video on YouTube.

This is code that we use to generate permanent random numbers in the relevant columns quickly for the East region:

Sub RandomNumbersEast()
For i = 1 To 12
ranNum = Int((36000 – 27000 + 1) * Rnd + 27000)
If Application.CountIf(Range(“B:M”), ranNum) > 0 Then
GoTo back
Cells(2, Columns.Count).End(xlToLeft).Offset(0, 1).Value = ranNum
End If
End Sub

Further reading: Excel Dashboards and Reports For Dummies (For Dummies (Computer/Tech))

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.