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.
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:
- The number of rows to move from the reference cell.
- The number of columns to move from the reference cell.
- 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.
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.
Static Interest 