HOW TO… Excel: Create a hierarchical series of drop down boxes

As a result of prodding around the excel subreddit I found a query from user innocuous_gorilla about creating drop downs that are dependent upon each other. Due to working at NHS England where we have a massive array of organisation types and hierarchies, I quickly learnt how to do this! For example, in NHS England we have different regions, sub-regions and commissioning groups which tend to be massive long lists if un-filtered, so it makes it hard to select one in a drop down where all options available. Using the following method, you can make a set of drop downs that all depend on a master drop down(s), so choosing a region or sub-region filters the commissioning group drop down box to only those groups that belong to the chosen parent region.

Here’s the link to the workbook example I’ll be walking through in the following tutorial, where we’ll deal with the example brought up by innocuous_gorilla.

We’ll tackle the workbook a section at a time, where I’ll describe what each part is for and how it works.

Part 1

Pic 0

This is our named range index. We’ve included this to make it a bit easier to track what named ranges are available within the spreadsheet, and what each named range is responsible for. I’ll be referencing these later as these are used with our combo boxes.

Part 2

Pic 1.PNG

Here we’ve simply typed a list for each of our options. The product list will be used to drive our first drop down (all drop downs used in this example are Combo Boxes), so we’ve saved this as a named range (follow this example in my previous tutorial to understand how to make a named range if you are unaware).

Part 3

Pic 2

Next up to the right of these lists, we’ve got a couple more lists which separate out the potential options that each of our products has available, note these are all differing lengths which makes it a bit harder to deal with when creating our dynamic lists.

Part 4

Pic 3

Next, we have a table giving us the maximum number of each option available to each of our products. This will allow our colour and size lists to populate dynamically and be the correct length. This can be generated by counting the tables in part 3 but were typed this time as it was easy to do so.

Part 5

Pic 4.PNG

Now we have our combo boxes – these have been populated using our named ranges. Each of the ‘List’ named ranges informs the corresponding combo box, and the ‘LinkCell’ named ranges are the Cell linkage for each corresponding combo box (if you’re lost check my other tutorial that covers how combo boxes work). The grey cells to the right of the drop downs are our ‘LinkCell’ and ‘Selected- -‘ named ranges. The ‘Selected’ named ranges have the following formulae:

=IFERROR(INDEX(ColourList,ColourLinkCell),INDEX(ColourList,$E$20))

This is indexing our ‘List’ named range and selecting the colour that corresponds with the linked cell. If this ever causes an error (which will happen when a lower-level drop down (in this case our colour and size drop downs) has a linked cell that is out of the range of the index, which occurs when changing between products if the product’s max number of colours/sizes is less than the previous selected product), we simply select the max possible option within the list (E20 is our max number of colours of the selected product).

Part 6

Pic 5.PNG

Finally we have our Colour and Size lists. The yellow cell to the left (both follow the same structure) has the following formula:

=INDEX($B$14:$B$16,ProductLinkCell)

Here we’re simply checking the table of maxes for the max number of the colours of the selected product, by indexing the column of colours based on the chosen product row number.

The counting numbers on the left of each list have the following formula:

=IFERROR(IF(E21+1>$E$20,"",E21+1),"")

This counts to the max number of the currently selected product for colours/sizes. We dragged this down to the potential largest max number for each option (4 for colours and 3 for sizes). If there’s an error or the number is greater than the max, we instead show a blank (“”).

The lists themselves are made up of the following formula:

=IFERROR(INDEX($H$3:$J$6,$E21,MATCH(SelectedProduct,$H$2:$J$2,0)),"")

This indexes the tables in part 3, looking for the row number corresponding to the cell to the left of the current cell and looking for the column corresponding to the currently selected product. If an error occurs, we show a blank (“”).

Part 7

These lists are then turned into the ‘List’ named ranges which use the following formula:

=Sheet1!$J$21:INDEX(Sheet1!$J$21:$J$23,Sheet1!$I$20)

This begins an array at the start of the list, then indexes the entire list to end the array at the maximum possible number for the selected product.

Hopefully understanding how each element works will help you to break this down and apply it to your own work! Thanks so much for reading and I hope this helped. If you have any further queries please ask in the comment section below.

Advertisements
How to How to... Excel

HOW TO… VBA: Compare VBA Combo Box Values with Their Lists

Preamble

So, a recent issue I’ve had using VBA to create UserForms to handle data input is data validation. Throughout my UserForm I’ve included a variety of Combo Boxes that contain lists of information the user can select. In most instances I would like the user to have inputted exactly one of the options within the drop down provided with the combo box; VBA has its own property to handle whether Combo Boxes require an exact match – the MatchRequired property. While this can be used, it creates an issue where if a user leaves a combo box unfilled the combo box will say there is a no match and therefore an error – locking the user out of the form until they input something that is a match. My UserForm is used for modifying and inputting requests our team has had, therefore certain Combo Boxes won’t be able to be filled until a request itself has been completed, with fields such as ‘Quality Assured By:’ only being relevant when the work done has been QA’d. In this instance this Match Required error would cause the user to be forced to enter incorrect data to proceed with the form if they’d accidently clicked into this combo box.

To get around this I turned off the MatchRequired property and created my own data validation Subroutine to check if the value held by each combo box matches a value within the combo box’s list. To do this we will need to create an array of arrays and then iterate through those arrays, something that’s fairly straight forward in more robust languages like Python and C# but a bit more finicky in VBA. Below is some example code which we’ll go through step by step.

Code

Sub ExampleDataValidation()
Dim failBools(0 To 2) As Boolean
Dim fail As Boolean
Dim errorMsg As String
Dim tempArray() As Variant
Dim comboBoxes(0 To 2) As Object
Dim comboValue(0 To 2) As String
Dim comboList(0 To 2) As Variant
Dim i As Integer, j As Integer
Dim matchChecker As Boolean

fail = False

For i = 0 To 2
    failBools(i) = False
Next i

errorMsg = "The following fields are incorrect:"

Set comboBoxes(0) = UserForm1.ComboBox1.Object
Set comboBoxes(1) = UserForm1.ComboBox2.Object
Set comboBoxes(2) = UserForm1.ComboBox3.Object

For i = 0 To 2
    comboValue(i) = comboBoxes(i).Value
    ReDim tempArray(0 To comboBoxes(i).ListCount - 1)
    For j = 0 To comboBoxes(i).ListCount - 1
        tempArray(j) = comboBoxes(i).List(j)
    Next j
    comboList(i) = tempArray
Next i

For i = 0 To 2
    matchChecker = False
    
    For j = 0 To 2
        If comboValue(i) = comboList(i)(j) And comboValue <> "" And matchChecker = False Then
            matchChecker = True
        End If
    Next j
    
    If matchChecker = False AND comboValue(i) <> "" Then 
        failBools(i) = True
    End If

Next i

For i = 0 To 2
    If failBools(i) = True Then
        fail = True
        If i = 0 Then
            errorMsg = errorMsg & vbNewLine & "ComboBox1"
        ElseIf i = 1 Then
            errorMsg = errorMsg & vbNewLine & "ComboBox2"
        Else
            errorMsg = errorMsg & vbNewLine & "ComboBox3"
        End If
    End If
Next i
    
globalVars.dataValidationFail = fail

If fail = True Then
    MsgBox errorMsg
End If

End Sub

We’ll separate each element of this code into distinct chunks and then explain each process line by line. Some basic level of VBA is assumed like how For loops work and how arrays work.

First, we start by declaring our variables:

Dim failBools(0 To 2) As Boolean : Here is our Boolean array, this is how we determine which 
                                   combo box has an incorrect value when constructing an error message. 
                                   We’ve used 0 to 2 as our indices because we are only going to be looking at 3 combo boxes (0, 1, and 2). 
                                   The upper bound will need to be set to however many elements your going to be checking for this and all other arrays.

Dim fail As Boolean: A bool that will be triggered if any of the failBools array are true, we’ll pass 
                     this to a global variable that can be accessed by other subroutines to allow other subroutines to respond
                     to the results of this subroutine.

Dim errorMsg As String: Here’s the string we’ll store our error message in.

Dim tempArray() As Variant: Here’s a temporary array we’ll use to construct and pass the lists of each 
                            of the combo boxes to our array of arrays.

Dim comboBoxes(0 To 2) As Object: An array of objects we’ll store our combo box objects within. 
                                  This allows us to more easily iterate over each of the objects (less typing and cleaner code!).

Dim comboValue(0 To 2) As String: An array of strings, for holding the values of each of the combo boxes.

Dim comboList(0 To 2) As Variant: Here’s the main event, our array of variants (a data type that 
                                  VBA determines on the fly) that will act as our array of combo box lists.

Dim i As Integer, j As Integer: These are our two integers we’ll be using to iterate through our For loops.

Dim matchChecker As Boolean: This bool will be turned to True if we find a match between 
                             a comboValue and a comboList.

Next, we need to set-up some of our variables values:

fail = False

For i = 0 To 2			This section is just resetting our fail Booleans before the 
    failBools(i) = False        subroutine begins, so we don’t accidentally store old values.
Next i

errorMsg = "The following fields are incorrect:" : This is just setting up the start of our error
                                                   message.

Set comboBoxes(0) = UserForm1.ComboBox1.Object	Here we are setting the comboBox
Set comboBoxes(1) = UserForm1.ComboBox2.Object	array to each of the combo boxes in
Set comboBoxes(2) = UserForm1.ComboBox3.Object	our UserForm.

Now we construct our first For loop.

For i = 0 To 2 
    comboValue(i) = comboBoxes(i).Value		The combo box value at index i is 
                                                stored at index i of our combo value array.

    ReDim tempArray(0 To comboBoxes(i).ListCount - 1)	Here we set our tempArray up to the 
                                                        correct Upper Bound of the current list. Combo box list count starts at 1, 
                                                        whereas we start at 0 so we need to take 1 away from the 
                                                        total count to create the correct length array.

    For j = 0 To comboBoxes(i).ListCount – 1		Now we iterate through the list of the combo box at index i.
         tempArray(j) = comboBoxes(i).List(j)	        We’re setting each element of tempArray to be equal to the corresponding element in the combo box’s list.
    Next j

comboList(i) = tempArray	Finally, we set the comboList at index i to be the newly constructed array.
Next i

Our next For loop will be where we determine whether a combo box value matches one of the values within that combo box’s list.

For i = 0 To 2					
matchChecker = False	At the start of each loop we reset the matchCheker bool.
    
    For j = 0 To 2
        If comboValue(i) = comboList(i)(j) 	If there’s a match between the current combo box value and the value of the same combo box’s list’s j-th index AND 
        And comboValue <> "" 			The combo box value isn’t blank AND
        And matchChecker = False Then	        The matchChecker hasn’t already found a match THEN
            	matchChecker = True             Set matchChecker to True as we found a match.
        End If
    Next j
    
    If matchChecker = False AND comboValue(i) <> "" Then	If the checker couldn't find a match and the comboValue isn't blank, 
        failBools(i) = True			                then set the corresponding failBools index to True.
    End If

Next i

Our final For loop will construct the errorMsg based on which failBools were triggered.

For i = 0 To 2
    If failBools(i) = True Then
        fail = True	This fail variable is set if any failBools were triggered so we can change a global bool after this loop.
        If i = 0 Then
            errorMsg = errorMsg & vbNewLine & "ComboBox1"	Using the index we can work 
        ElseIf i = 1 Then					out which combo box is
            errorMsg = errorMsg & vbNewLine & "ComboBox2"	causing our issue, adding it to 
        Else							the error message if it
            errorMsg = errorMsg & vbNewLine & "ComboBox3"	was triggered.
        End If
    End If
Next i

Finally, we trigger a global bool variable and create our error message. The global bool can be used by other subroutines to work out whether they need to continue as a result of this data validation subroutine.

globalVars.dataValidationFail = fail	globalVars is simply the name of the module 
                                        we are using to store all global variables in
If fail = True Then	                the project.
    MsgBox errorMsg
End If

This subroutine can be run when leaving the UserForm and committing the inputted data to the WorkBook. Upon any failures, we can prevent the UserForm from continuing to exit until our user has either removed the incorrect data or corrected it.

Thanks so much for reading, I really hope this helped. If you have any further queries regarding this method, please ask in the comments section below.

How to How to... Excel how to... vba

HOW TO… Excel: Create a Dynamic Data Range for a Chart

Continuing the theme of automatically updating charts, we’re going to look at how to make a chart deal with a dynamically changing data range. This is useful for situations where you have multiple measures that each have a different number of independent variables (the variables plotted on our x-axis, which was the month of the year in our last example). If you use the method as lined out in the last tutorial for this you will find that the chart updates automatically, but the number of variables shown on the x-axis will not change. To solve this, we will need to use Named Ranges; OFFSET; and COUNT to produce the ranges that will feed the automatically updating charts.

We’ll be continuing with the workbook used in the last tutorial (downloadable here) for our example.

Step 1.

Step1 Example

First, we’ll need to create our Named Ranges. We will need a named range for our horizontal axis labels and for our data. We’ll name these ‘ChartDateRange’ and ‘ChartOrgRange’. Now we’ll need to construct the OFFSET function that will be used to determine the ranges of the data dynamically. For both named ranges we will be using almost the same formula, just pointed at a different reference cell. Our ‘ChartDateRange’ will be as follows:

=OFFSET(Charts!$C$30,,,,COUNT(Charts!$C$31:$CF$31))

The first parameter is the ‘Reference Cell’; C30 – the starting cell of our date ranges. We will then pass through the next 3 parameters which are as follows:

  1. The number of rows to move from the reference cell.
  2. The number of columns to move from the reference cell.
  3. The height of the range starting from the reference cell.

Which means the final parameter with our COUNT function determines the width of the range starting from the reference cell. We count all the data within the data range; in this instance I’ve extended the range of the COUNT to include extra columns. We will be extending our data range further out to allow the chart to handle extra data in the backing sheets and adding in additional months of data.

Our ‘ChartOrgRange’ is then simply:

=OFFSET(Charts!$C$31,,,,COUNT(Charts!$C$31:$CF$31))

We’ve just shifted the reference cell down one row to C31 in this case.

Step 1b.

We’ll quickly extend the data ranges of our back sheets and our INDIRECT named range ‘SelectedData’ to also cover the new extended data range. We’ve extended the back sheets to column CF and made the blank data cells show an error by using:

 

=NA()

 

This is necessary to prevent us counting these cells as they will show as 0s when not errors.

 

To match this, we’ve extended ‘SelectedData’ to include columns CF, so the formula is now:

 

=INDIRECT("'"&'Output Sheet'!$H$16&"'!$C$7:$CF$16")

 

We also need to change our chart’s data table to have the formula:

 

=INDEX(SelectedData,OrgNameLinkedCell,MATCH(C$30,AverageTimeData!$C$6:$CF$6,0))

 

We’ve changed the MATCH function to now look at the range C6:CF6 in our back sheets. We then need to extend the data table all the way across to column CF too.

Step 2.

Step 2 Example

Now we can set the chart to look at these Named Ranges instead of the actual data range.

Right click the chart and go to ‘Select Data’. First, we’ll assign a new series data range, so click the ‘Legend Entries’ option for our current series then select ‘Edit’. Change the ‘Series values’ to:

=Charts!ChartOrgRange

It’s important to include the worksheet reference ‘Charts!’ as otherwise the chart will not accept the new input.

Next, we’ll change the horizontal axis labels range, click the ‘Edit’ button for ‘Horizontal (Category) Axis Labels’. Change the ‘Axis label range’ to:

=Charts!ChartDateRange

Once again, make sure you include the worksheet reference ‘Charts!’.

Step 3.

We can now add in new data to test our changes. To showcase this, we’re going to add in some new dummy data to just one of our back sheets; ‘StaffData’ in this case.

To achieve this, we’ve added in 2 more months of data using:

 

=RANDBETWEEN(20, 500)

 

This generates some random numbers for us between 20 and 500. To ‘freeze’ these values we’ll just copy them and paste them as values.

Once this is done you should be able to change freely between the measures and note that for Number of Staff we have data all the way up to May-17, and for the other two measures they only extend up to Mar-17.

And that should be it! This method is also especially useful for automation of Excel reports. If you set up the back sheets to automatically pull their data from wherever their data source is, utilising this method will allow your front end to also automatically update meaning you will have more time to check your data and free up your time to do more proper, less tedious work!

You can download the completed workbook here.

Once again thanks so much for reading and I really do hope this helps. If you have any comments or queries about this tutorial, please do reply below or email us at staticinterest@gmail.com.

How to How to... Excel

HOW TO… Excel: Create an Automatically Updating Chart

Following on from our last series of HOW TO… Excel, we’re going to expand the concepts used to create the dynamic data table and create another sheet for the same workbook that will be the home of an automatically updating chart! Unlike pivot charts, this will be able to update as soon as new data is inputted into the data backsheets, and will be connected to the same ‘linked cell’ as our previous combo box, so the changes will be persistent throughout the workbook. This will allow us to create a consistent and pleasing user experience for whom ever will be using the tool.

We’ll be using the same workbook as the first tutorial so if you want to follow along please do download it here.

Step 1.

We need to set-up a measure input list and the linked cell used for our combo box as named ranges to make it easier to copy and paste the combo box and reference these ranges throughout our workbook.

Go to ‘Formulas‘ -> ‘Define Name‘ and define two new names, one pointing at ‘Output Sheet’!G16 (our linked cell) and the other at the range ‘Output sheet’!A16:A18 (our input list). We’ve called these our ‘MeasureLinkedCell’ and ‘MeasureInputRange’ respectively. These can be named however you would like but its important that it’s relevant to the task its being used for to make them easier to unpick for yourself and other people in the future.

Step 2.

Now we will set up a combo box using these two ranges (in this example we are going to be changing the existing combo box used for our ‘Output Sheet’). If you can’t remember how to do this please check the earlier tutorial as it covers this.

Step2

We’ll now create a new sheet to house our chart(s). We’ve called it simply ‘Charts’ in this example. Copy the combo box to this sheet; as we’ve used named ranges the links should remain intact to the old linked cell and input range. If you change the selection of this new combo box, it will also change the combo box on the other page as they are both linked to the same cell.

Step 3.

We will also need a new combo box to be used for our organisations as showing all 10 organisations in this case would create a very unwieldy chart. In this example we’ll be showing how to set-up a line chart that will drill down to different organisations and measures. Utilising the principles from this tutorial you should be able to create any type of chart you’d like, and tailor pick the data to create the chart.

Again, we will set-up some named ranges for our input range and linked cell for our organisation list. Due to the increasing number of lists we are accessing it may be worth moving these to their own sheet to create a more manageable workbook. We’ll be creating a new sheet called ‘Lists’ to do this and moving our previous named range references here too.

List Sheet 1

Side note: Our old linked cell (‘Output Sheet’!G16) for our output sheet will need to point to the named range now so that our output sheet continues to work.

Create the new named ranges for the organisation name list and the organisation name linked cell, in this case I’ve named these ‘OrgNameInputList’ and ‘OrgNameLinkedCell’. Back on the ‘Charts’ sheet we’ll create that new combo box and linked it to these named ranges. So far you should have a new Charts sheet with two combo boxes, one linked to measure names and another linked to organisation names. Here’s what I have so far, with some formatting at a basic level.

Charts Sheet 1

Step 4.

Now we need a dynamically updating data table that will take the input from the linked cells of these combo boxes to create the data array that will feed the chart on this sheet. With our named ranges and the INDEX function we can create some nifty looking formulae that retrieve the organisation name and measure name based on the linked cells. So to get the organisation name we need a cell with: =INDEX(OrgNameInputList,OrgNameLinkedCell))
And for the measure name:
=INDEX(MeasureInputList,MeasureLinkedCell)
To get the sheet name for our measures we’ll add in another section to our ‘Lists’ sheet with a measures sheet name list as follows:

List Sheet 2

Then we can add in another cell to find the sheet name with the following INDEX function:
=INDEX(Lists!$E$2:$E$4,MeasureLinkedCell)

Step 5.

Now to create the dynamic table it’s the same as in the previous tutorials using whatever methodology you would prefer (using INDIRECT, OFFSET or INDEX with/without the master data back sheet, see my other tutorials for more info). Here we’re going to use the INDIRECT method to create this dynamic data table. We’ve already got an INDIRECT named range that looks at the chosen data back sheet, which was
=INDIRECT("'"&'Output Sheet'!$H$16&"'!$C$7:$Z$16")
where H16 was our selected measure sheet.

So, we can create the data table to look as follows:

Charts Sheet 2

Using the formula
=INDEX(SelectedData,OrgNameLinkedCell,MATCH(C$30,AverageTimeData!$C$6:$Z$6,0))
where C30 is the reference to our dates row and AverageTimeData!C6:Z6 is the reference to the dates list that all the data back sheets utilise.

We don’t need to do a MATCH for the row number as all of our back sheets use the same list of organisations, so we can use the Linked Cell as that is the row number of the chosen organisation.

Step 6.

We’ll now create a line chart that uses this dynamic data table. Highlight the data table, and go to ‘Insert‘ -> ‘Charts‘ -> ‘2D Line Chart‘ to create the line chart. If you now use the combo boxes to select different combinations of measure and organisation you’ll see that the chart updates in response. The next steps are to tidy up the chart to make it more readable and understandable for our users.

Step 6a.

First, we will make the chart title update with the change of measure and organisation name. We will need to create a chart title in another cell that will be referenced by the chart title. In a cell nearby our data table (H27 in my case), add a formula such as this:

=$C$27&": "&$C$28

Where C27 is the chosen organisation name and C28 is the chosen measure name. This will create an output that looks like this: “Organisation: Measure”. We use ampersands (&) to allow us to mix strings (sections of formulae surrounded by quotation marks “”) and cell references. Now we need to link the chart title to this cell. Click into the chart title, navigate to the formula bar and type

=Charts!$H$27

Or click into the relevant cell that contains the new chart title after typing ‘=’.

Step 6b.

Next, we need to create a new vertical axis title to contextualise our data. For this we’ll need another list in our ‘Lists’ sheet that will contain the units of measurement for each of our measures. For this sample data set we need Hours, Units of Product (or just ‘Units’ for short) and No. of Staff for Average Time Taken, Total Throughput and Number of Staff respectively. We’ll then create a link to this list that provides us with the unit of measurement for the chosen measure using the formula:
=INDEX(Lists!$G$2:$G$4,MeasureLinkedCell)
where Lists!G2:G4 is the range for the ‘Units of Measurement’ list. We’ll now create a vertical axis title by selecting the chart, clicking the green plus that appears in the top right, navigating to Axis Titles and adding a Primary Vertical Axis Title.

Charts Sheet 3

Now click the axis title that appears, navigate to the formula bar and type:

=Charts!$H$28

Or click into the relevant cell that contains the axis title after typing ‘=’.

We should now have a working dynamic chart that updates the title and axis title when the measure and organisation name get changed.

You can now adjust the chart to your liking (preferably following good data visualisation principles!) but functionally everything should be working fine.

Next time we’ll discuss using OFFSET to create a dynamically adjusting data range for the chart so that it will automatically update when new data is added to the data set and creating methods to drill down into our data using different criteria.

As always thanks for reading, if you have any comments of your own or ways you’d do this instead please do let me know in the comment section below!

The final version of the workbook used is available here.

How to How to... Excel

HOW TO… Excel: A Truly Non-Volatile Solution for Dynamic Data Tables

This is a follow-up to the last tutorial. Thanks to reddit user /u/small_trunks for noticing my blunder!

Unfortunately, ‘SUMIFS’ couldn’t be used on its own to create the dynamic data tables. But don’t fret! I have another solution up my sleeve that doesn’t rely on volatile functions like ‘INDIRECT’ and ‘OFFSET’! It relies entirely on ‘INDEX’ and ‘MATCH’.

We’ll need to add an additional list next to our measure names and back-sheet names lists on our output sheet, which I’ve named the ‘Master Sheet Start Point’ column. In this column we can use the ‘MATCH’ function to work out the first iteration of each of the measure names in our backsheet. The formula in this column is:

=MATCH($A16,MasterBacksheet!$C$2:$C$31,0)

Then, below our two cells that work out which sheet name and measure name (H16 and H17) is corresponding to what has been selected by the combo box we can add another cell that will find the starting point of the selected measure in the master back-sheet. The formula will therefore be:

=INDEX($E$16:$E$18,$G$16)-1

We need to include the ‘-1’ as we’ll also be using ‘MATCH’ in the main formula for finding the row number of each organisation so we don’t want to ‘double count’.

NewSolution

The formula we’ll be using in the dynamic data table will be an ‘INDEX’, ‘MATCH’ formula as follows:

=INDEX(MasterBacksheet!$D$2:$AA$31,MATCH(‘Output Sheet’!$B4,MasterBacksheet!$B$2:$B$11,0)+’Output Sheet’!$H$18,MATCH(‘Output Sheet’!C$3,MasterBacksheet!$D$1:$AA$1,0))

Breaking this down gives us:

  1. =INDEX(Full Array of Master Back-sheet Data,
  2. MATCH(Org name, Org Name list in Master Back-sheet, Look for an exact match) + Measure’s Master Sheet Starting Point,
  3. MATCH(Date, Date Headings in Master Back-sheet, Look for an exact match))

And then this formula can be copied across the entire data table! With that we’ve solved the issue, we have a non-volatile method of creating a dynamic data table.

NewSolution2.PNG

Once again if anyone has any other methods of solving this solution please share in the comment section below – I’d love to see what creative methods the community can come up with!

The workbook can be downloaded here.

How to How to... Excel

HOW TO… Excel: Using SUMIFS to Create a Dynamic Data Table

This is a continuation of the previous tutorial.

Due to the volatility of the INDIRECT function, it may be necessary to avoid using it when trying to create large dashboards which will be handling and outputting large amounts of data. So, we’ll need an alternative in these cases. One of the ways this can be done is by using the SUMIFS function, and structuring our data to be compatible with the SUMIFS function.


This has been achieved using SUMIFS, but OFFSET (another volatile function) has been used to cover one of SUMIFS weaknesses. A completely non-volatile method can be found here.


This will be done on the worksheet from the previous tutorial. A download link is available at the bottom of that page so you can follow along.

Step 1.

First, we’ll need to create a new ‘master’ data back-sheet that compiles all the data back-sheets to allow SUMIFS to work correctly.

Step1.PNG

A new column called ‘Measure’ has been added to the old structure which contains the measure names, and the data has been pulled from the other back-sheets by simple cell references.

Step 2.

Now we need to change the formula in the output sheet’s data table to use SUMIFS. But before that, we’ll need add a new INDEX that checks the Measure Name selected by the combo box.  H16 on the output sheet can be copied down to H17 and then have its INDEX array changed to the measure name list as opposed to the back-sheet name list so the new formula in H17 is:

=INDEX($A$16:$A$18,$G$16)

We can now change the dynamic data table’s formula. The formula used in the top left (C4) of the table is as follows:

=SUMIFS(MasterBacksheet!D$2:D$31,MasterBacksheet!$C$2:$C$31,’Output Sheet’!$H$17,MasterBacksheet!$B$2:$B$31,’Output Sheet’!$B4)

If we break this down we get:

  1. =SUMIFS(SUM_Range , Criteria_Range1 , Criteria1, Criteria_Range2, Criteria 2)
  2. =SUMIFS(Apr-15 Column (frozen to row numbers using absolute referencing, $), Measure Column, Selected Measure, Org Name Column, Org Name (frozen to column number using absolute referencing, $)

A weakness of this formula as it stands is the way we’ve referenced the SUM range, using direct references. To allow this formula to be more flexible we can instead use another function to work out the SUM range instead of directly referencing it. One way to do this is to use the OFFSET function. The way to construct the OFFSET function within this formula to correctly determine the SUM range dynamically is as follows:


As pointed out by reddit user /u/small_trunks, OFFSET is also volatile so isn’t a good alternative to the ‘Indirect’ function! This is still a valid method to create the dynamic data table if volatility is not an issue, but a better method can be found here – although unfortunately ‘SUMIFS’ hasn’t been used in this instance.

UPDATE: Reddit user /u/ubbm has come to the rescue! The example below with OFFSET can be replaced with an INDEX function. INDEX can be used in a very similar way to OFFSET; if you specify an array and then only choose a row number or column number, the output will be the entire row or column within the array. So for this example the following can be used instead of OFFSET:

=INDEX(MasterBacksheet!$D$2:$AA$31,,MATCH(‘Output Sheet’!C$3,MasterBacksheet!$D$1:$AA$1,0))

See the full comment for a break down of the logic behind this method of using INDEX.


=OFFSET(MasterBacksheet!$C$2:$C$31,,MATCH(‘Output Sheet’!C$3,MasterBacksheet!$D$1:$AA$1,0))

The structure of the OFFSET function is:

  1. =OFFSET(Reference Range, Number of Rows to Move from Reference, Number of Columns to Move from Reference, [OPTIONAL] Height of the Reference, [OPTIONAL] Width of the Reference)
  2. So, our ‘OFFSET’ function is: = OFFSET(Measure Name Column (1 column before the date columns), Move 0 Rows, Find ‘Apr-15’ in the date headings and move that many columns across). We start one column before the date headings as ‘MATCH’ returns 1 if it finds a match in the first cell of the range.

So our full formula is:

=SUMIFS(OFFSET(MasterBacksheet!$C$2:$C$31,,MATCH(‘Output Sheet’!C$3,MasterBacksheet!$D$1:$AA$1,0)),MasterBacksheet!$C$2:$C$31,’Output Sheet’!$H$17,MasterBacksheet!$B$2:$B$31,’Output Sheet’!$B4)

This formula can then be copied across the entire data table.

Step2

And there we have it! This of course is just one way of going about solving this issue, and part of the beauty of Excel is the ability to bend it to your will when you know all the different functions and tricks of the trade. If you have any other ways in which you would go about creating a dynamic data table, you can share it in the comment section below. Any feedback on my methodology is also welcome!

You can download the finished workbook here.

How to How to... Excel

HOW TO… Excel: Create a Dynamic Data Table Using INDIRECT and Combo Boxes

IndirectTutorialGif

Creating a full and cohesive user experience in an Excel based dashboard can be really difficult if you don’t know where to start. Often, the easiest way to approach the situation will be to utilise Pivot Tables and Pivot Charts, which come with useful tools like slicers to dynamically filter the data with a touch of a button. I have a few pet peeves with Pivots however, mainly being that they require data be in a particular layout; that they can be restrictive on the type and layout of the output they produce (although they do provide a good level of options); and when updating or changing data within a spreadsheet Pivoted data does not refresh unless it’s explicitly told to (which can create a jarring experience when handling and presenting larger data sets).

So, how would you go about creating a dynamic data table and dashboard without using Pivot Tables? There are a seemingly massive number of ways to go about this task, but the way I find most versatile and that I’ve become comfortable using is to make use of Combo Boxes and different Excel functions like INDIRECT (covered in this tutorial), OFFSET, SUMIFS and INDEX.

In this tutorial I will show how to utilise combo boxes and the INDIRECT function to create a dynamic data table which can be used to better visualise your data and be used as a solution for a client or manager-facing reporting tool. This tutorial will be using dummy data to show a basic Key Performance Indicator dashboard with data from April 15 to March 17. The INDIRECT function will be used specifically to dynamically reference all of our backing sheets.

Step 1.

First we’ll need to create structured backing sheets which follow a consistent lay-out. This is essential to allow the INDIRECT function to consistently output the correct arrays for our data.

Step1

The above example shows my preferred structure:

The top-left of the sheet includes a quick description of where we got the data from and how up-to-date our data is. Including this is general best practice for maintaining data back-sheets to allow other people to understand the data’s history and for us to better quality assure our data. All three of the back-sheets in this workbook will follow the same format, including the same organisation list and dates.

Step 2.

Now we’ll create the output sheet that will hold the dynamic data table. In this example I’ve copied the same structure used in the back-sheets, with the same organisations and all the dates. It would be possible to pick and choose dates and organisations for this output sheet, as the formula used will be able to deal with any combination of the 2 variables.

Step2

Step 3.

We can now create the combo box and the lists of measures and sheet names that feed the combo box. To create a combo box, go to the developer tab of the ribbon

(If the developer tab of the ribbon isn’t visible for you, go to:
File -> Options -> Customise Ribbon
then on the list on the right hand side of this tab tick the selection box for the developer tab).

From the developer tab, select Insert and under ‘Form Controls’ select the combo box option. You should then be able to draw a combo box on the spreadsheet.

Step3a

Now we will create 2 lists: one of measure names and one corresponding to each of our back-sheets. In this example we have 3 measures:

  1. ‘Average Time Taken’;
  2. ‘Total Throughput’; and
  3. ‘Number of Staff’

Corresponding to our back-sheets:

  1. ‘AverageTimeData’;
  2. ‘TotalThroughputData’; and
  3. ‘StaffData’

I’ve created the lists next to one another below the data table as below:

Step3

Step 4.

Now we need to link the combo box we created earlier to the measure list. Right click the combo box and select ‘Format Control’. For the ‘Input Range’ select the measure list; in this example it’s A16:A18. For the ‘Cell Link’ select an empty cell nearby; in this example the cell chosen was G16. Hit OK, the combo box should now allow you to select the measures in the measure list, with the linked cell showing the number corresponding to the measure’s position in the list. In the cell beside the linked cell we will now use an INDEX function to work out the sheet name from the list created beside the measure names.

Step4a

INDEX looks at an array and works out the position of a cell based on a given row and column number. As the linked cell gives the position of the measure in the measure list (thus acting as the row number), it will also return the corresponding back-sheet if we use it in an INDEX function.

Step 5.

Now we can construct the INDIRECT function; in this example it will be done within a named range, but it’s also possible to do this within a cell’s formula. Using named ranges just cleans up the final formula and makes it easier to parse for other users. To create a new named range go to the ‘Formulas’ tab of the ribbon and select the ‘Define Name’ option. Give the named range an appropriate name like ‘SelectedData’, and insert the INDIRECT function. In this example the formula will be:

=INDIRECT("'"&'Output Sheet'!$H$16&"'!$C$7:$Z$16")

The purpose of the INDIRECT function is to allow excel to interpret text as an array or cell. Breaking down this formula gives us the following:

  1. =INDIRECT(“TEXT TO BE INTERPRETED”)
  2. =INDIRECT(“ ‘ “& CELL WITH SHEET NAME &” ‘! DATA TABLE’S ARRAY”) this is then interpreted by excel as:
  3. =’SHEETNAME’!DATA TABLE’S ARRAY
Note: The data table’s array does not include the column headings or the row headings i.e. the dates or the organisation names – it starts at the actual data.

Step5

Step 6.

We can now construct the formula used in the dynamic data table. We’ll be using an INDEX function, in conjunction with our ‘SelectedData’ named range and the MATCH function.

The formula in the top left of our dynamic data table is:

=INDEX(SelectedData,MATCH($B4,$B$4:$B$13,0),MATCH(C$3,$C$3:$Z$3,0))

Breaking this down to each part gives us:

  1. =INDEX(SelectedData, this is the link to our named range, so our named range is the array we are ‘indexing’.
  2. MATCH($B4,$B$4:$B$13,0), this is where we work out the row number to look for. B4 is our first organisation name ‘Supplier A’ (its column is ‘frozen’ by using the ‘$’ before B), and the array B4:B13 is the full list or organisation names. The 0 shows we’re looking for an exact match. MATCH then returns the row number of ‘Supplier A’ in the list of organisation names; 1 in this case. The array (the list of organisation names) in this function is able to be the list on the output sheet as it’s the same as the list in the data back-sheets. If you were to create a custom list of organisations to show, you would need to check B4 against the full list of organisations used within the data back-sheets.
  3. MATCH(C$3,$C$3:$Z$3,0) this is where we work out the column number to look for. C3 is our first date ‘Apr-15’ (its row is ‘frozen’ by using the ‘$’ before C), and the array C3:Z3 is the full list of dates. The 0 again shows we’re looking for an exact match. MATCH then returns the column number of ‘Apr-15’ in the list of dates; 1 in this case. As mentioned in the previous point, the array (the list of dates) in this function is able to be the list on the output sheet as it’s the same as the list in the data back-sheets.

This formula can then be copy and pasted across the entire data table. This should then result in a table that can dynamically update like so:

IndirectTutorialGif2

Step 7.

Now that we have a dynamically updating data table we can add in dynamically updating conditional formatting! Alongside the measure and sheet lists we can include a boundary at which our green turns to red and vice versa. I’ve set these to 2, 200, and no boundary for ‘Average Time Taken’, ‘Total Throughput’ and ‘Number of Staff’ respectively. Next to these lists I’ve included a cell that contains the boundary for the selected measure. This is done in the same way as the sheet name cell, using the INDEX function. For this example the formula is:

=INDEX($C$16:$C$18,$J$1)

This looks at the array of boundaries (C16:C18) and returns the one corresponding to the chosen measure.

Now you can highlight the data table, go to the ‘Home’ tab of the ribbon and select the ‘Conditional Formatting’ then ‘Highlight Cells Rules’ and ‘Greater Than…’.

Step7

Select the cell that contains the boundary for the selected measure; D16 in this case. Choose the ‘Light Red Fill with Dark Red Text’ option and hit OK. Do the same for ‘Less Than…’, selecting the same cell and this time choosing ‘Light Green Fill with Dark Green Text’. Sorted!

Step 8.

Now you may have realised upon changing the measures that these don’t make sense for our ‘Total Throughput’ measure and the ‘Number of Staff’ measure is incorrectly showing the conditional formatting. To combat this we’ll need to change the rules to an IF statement rather than a simple greater than or less than rule. To change the rules, go to the ‘Manage Rules’ section of the ‘Conditional Formatting’ menu. For the Red formatting, select ‘Edit Rule’ and change the rule to ‘Use a formula to determine which cells to format’. The formula used in this example is:

=IF($H$16="TotalThroughputData",C4<$D$16,C4>$D$16)

This checks the selected data back-sheet, and if it’s the back-sheet for the Total Throughput data: highlight the cells that are less than D16 (our cell containing the selected measure’s boundary) red; and if it’s not highlight the cells that are greater than D16 red. This means that the rule now correctly shows those organisations with low throughput as red, instead of green.

Now edit the green formatting in the same way, using the formula:

=IF($H$16="TotalThroughputData",C4>=$D$16,C4<=$D$16)

This checks the selected data back-sheet, and if it’s the back-sheet for the Total Throughput data: highlight the cells that are greater than or equal to D16 (our cell containing the selected measure’s boundary) green; and if it’s not highlight the cells that are less than or equal to D16 red. This means that the rule now correctly shows those organisations with high throughput as red, instead of green. By using the ‘greater/less than or equal to’ inequality we have now also included the boundary’s number within the formatting which is another error that needed to be corrected.

The final error that needs to be corrected is that ‘Number of Staff’ will still show the conditional formatting despite not having a boundary. To correct this add a new rule with ‘Use a formula to determine which cells to format’, and add the formula that will exclude those measures with no boundary – in this example we’ve used ‘‘ to denote no boundary in the list of boundaries. The formula used is then as follows:

=$D$16="-"

Leave the format as ‘No Format Set’ and hit OK. Then tick the ‘Stop If True’ checkbox in the ‘Manage Rules’ options window for this rule.

The sheet is now complete from a functionality standpoint – I’ve made some general formatting changes to try and make the table clearer and easier to interpret. I’ve increased the row size of the table rows to 20, removed the gridlines on the sheet by going to ‘View’ on the ribbon and unchecking the ‘Gridlines’ box. I also added a title: ‘Select Measures’, above the combo box to help aid user experience. I’ve also used the ‘Freeze Panes’ setting in the ‘View’ tab at cell C2 to lock the organisation names and combo box in the view of the spreadsheet. I’ve hid rows 15-18 and added a filter to the column headings, by highlighting them all and then going to the ‘Home’ tab and selecting ‘Filter’ under the ‘Sort & Filter’ option on the right of the tab.

One thing to be aware of with this method is a weakness of the INDIRECT function. INDIRECT is known as a volatile function, this means whenever any change is made to the workbook it will cause any formulae with an INDIRECT function in it to recalculate. Other functions that share this property are the functions like RAND and RANDBETWEEN. This means incredibly large data tables which use INDIRECT can cause massive slow down when making changes to the workbook. A way to avoid the INDIRECT function would be to format the data back-sheets in an alternative way – with one master data back-sheet instead of multiple. I’ll cover an example of this in future tutorials!

So after following this method you should be able to create a dynamic data table perfect for Key Performance Indicator dashboards! It offers more flexibility (in my opinion) than Pivot Tables, and using combo boxes allows us to link multiple parts of a spreadsheet to a single dynamic cell to create a more cohesive user experience.

Thanks for reading, if you have any questions or feedback please do comment below!

You can access the workbook here.

How to How to... Excel