Updating excel graph
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.
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!
We’ll be using the same workbook as the first tutorial so if you want to follow along please do download it here.
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.
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.
We’ll now create a new sheet to house our chart(s). 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.