This week we will create a dynamic graph that shows the changing costs for each region over the years. The key element of this graph is to create an additional series for the region in focus, which changes when the user selects a different region.

Output 2

The graph is in the Output 2 tab on the Excel workbook here, members can access the unlocked version in the lounge.

The list box on the left allows the user to select which region they want to have the focus in the graph (the darker line)

You will recall that the list box can be found in the Developer tab.
The Format Object window (below) should appear when you create the list box, but if it doesn’t then right click the button and select Format Object

The ‘Input Range’ is the list we created in the set up sheet and ‘Cell link’ is B2 in the setup sheet in our example.
The selection type is ’single’, that means only one region can be selected at a time.

Before we create the graph we firstly need to add the ‘focus’ data series for the graph.

This is added in column N in the ‘Ho_maint’ tab with the formula
INDEX(C1:M1,1,setup!$B$2,1)

This simply copies the data from the column selected by the user (cell B2 in setup sheet).

Create the graph by using the line chart wizard using the data in setup (columns A to M) and it will look something like this.

A bit messy so let’s tidy it up a bit
• Delete the legend
• Extend the graph to the area vacated by the legend
• Change the colour of each series line to a light blue/grey
• Change the colour of the ‘focus’ series to a dark blue
• Add major gridlines

And that is it!

I hope you have found the blog of interest and I would welcome any comments, especially if you have alternative solutions.

See you in the members lounge.