Interactive Excel Chart using radio button and scroll bar

Dear Forum Members,

Looking for kind assistance and suggestion to plot a chart as show in the picture below, which allows to switch between view based on radio button selection and as well allows a scroll through feature to see the trending or progress over a certain period.

I have attached the dummy raw data and need help to formulate the data to make this chart.

Picture of intended chart

Proposed Chart.jpg

I think I have something that comes close. I did not figure out how to scroll the horizontal axis.

The solution depends on three named dynamic ranges. http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
List_Data_Center is a list of the data centers
List_LOB is a list of the LOBs.
List_Use is one of these two lists and is selected based on the value of cell F4 on the Lists Sheet

Cell F4 on the lists sheet is set by the radio buttons on the chart sheet.

Cell B9 on the chart sheet uses List_Use for data validation. This cell (B9) triggers a VBA change event that sets the filters on the pivot sheet. The pivot table runs the chart.

Thank you for the prompt response and apologies for delay in responding, for some reason, the page was not allowing me to post a reply.

I like the logic using the names ranges and VBA. But can you please advise something which in non-VBA. Requesting, as I have to create a few dashboards and copy pasting the logics may not help in long run and may get tricky for me.

For far at my end, I am still working on getting the data formulated for the chart. So as per below screenshot, I have got the data for the given data points except for counting the outstanding number of tickets for a particular month, for which I am facing issue.

Next steps for which I look for advise and help :-

1_Create dependency from Option Box to Combo-Box
2_Based on selection of these control boxes, have the data table sorted
3_Have something figured out for using the scroll bar

Data Calculation for Chart.jpg

The VBA is used to set the filters on the pivot table and there is no other way to do that. The pivot table makes the date displays flexible. Pivot tables only display information for which they have data.

There is another option, and that is to make a table with fixed dates. If there is no information for a date, the date will still appear on the x-axis, but have zero or no value on the y-axis. I will work on trying to set this up.

It starts with the pivot tables on the list sheets. A pivot table is easiest way to get a unique list of items in a table.

You will have to refresh these tables when you change the source data. Once refreshed, you are OK until you change the data again. Since both pivot tables are built from the same source, if you refresh one table, the other table automatically.

It is possible that you will wind up with a different number of rows whenever you change the data. So the pivot table results must be overlaid with a named dynamic range.

Here is more information on named dynamic ranges: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges

I created two names: List_Data_Center and List_LOB. I will go through List_LOB as an example
List_LOB =OFFSET(Lists!$C$4,0,0,COUNTA(Lists!$C:$C)-1,1)

The offset command has 5 auguments
- A start cell (C4)
- Number of rows to go down (0)
- Number of columns to go right (0)
- Size of the range to return (COUNTA(C:C)-1 rows and 1 column)

The reason for the -1 is that we don't want to count the header.

List_Data_Center has a similar definition.

Now let's go back to the radio buttons on the Chart Sheet. I had to look this up on line to see how to do it. The information is out there and it is fairly straightforward. The radio buttons are in a Group Box which means that they are mutually exclusive: click on one and the other one goes off. The Group box can be linked to a cell. In this case, cell F4 on the Lists sheet. This takes on the value of 1 if the top button is clicked or 2 if the bottom button is clicked.

Which leads us to the next named range which has the formula: =IF(Lists!$F$4=1,List_Data_Center,List_LOB) and is called List_Use. List_Used is used as the data validation for Cell B9 on the chart sheet. The radio button determines the value of Cell F4 and the if statement tells Excel which named dynamic range to use.

I needed to group the raw data by year and month. This can be done automatically by a pivot table, but we dropped using one for this purpose because of setting the filters. So I added in two helper columns to compute year and month for each Start Date.

I then created the Table on the Table sheet. This table is used to plot the data. The Year and Month column was created manually. Since it is a table if you were to add 2020 the chart will keep up.

I have some additional columns here:

-Axis Name is just to compute a Month-YY label to display on the chart.
-Count has the formula: =COUNTIFS(Table_Raw_Data[Data Center],$B$1,Table_Raw_Data[LOB],$B$2,Table_Raw_Data[Year],[@Year],Table_Raw_Data[Month],[@Month])
-Plot has the formula: =IF([@Count]=0,NA(),[@Count]) - the NA() produces #N/A which means, Do not plot this value.

You will note that the Count formula looks at cells B1 and B2. These two cells also take their values from Cell F4 on the list sheet. The asterisk (*) means match anything.

I highlighted the Axis Name and Plot columns and created the chart that I then moved to the sheet with the radio buttons and drop down list.

The X-axis shows all dates, even those without data. If you go back to the Table Sheet and filter out the #N/A rows you get only the months with data.