Pivot table/chart with slicers help

What you need is named ranges. Some of these ranges are dynamic and some will be static.

First off, congratulations on using an Excel Table. Excel tables have a lot of good features, several of which come into play with this issue.

The first of these features is that tables know how big they are and expand and collapse as data is added or deleted. If you want to make a drop down list based on a table column, the syntax you use in data validation is =INDIRECT("Table_Name[ Table_Column ]") so for this exercise, the data validation for cell A1 is =INDIRECT("Data[ Name ]") - this list is dynamic. As you add or delete rows the drop down list will change to keep pace.

The next two named ranges are dynamic. The first one is a bit weird. The name is Plot_Month and it is defined as =(Data[ #Headers ] Sheet1!$B$3:$AX$3)

Notice the space between Data[ #Headers ] and Sheet1!$B3:$AX$3 - this is a seldom-used feature of Excel. The space means "intersection". So I am looking for the intersection of the table header (columns A to L in the enclosed example) and columns B to AX. Effectively this is Columns B to L which are the months I want to plot. When you add Mar-19 to the table, the intersection will extend to column M and so on up to column AX.

The next dynamic range is Plot_Value and it is defined as =OFFSET(Plot_Month,Sheet1!$B$1,0)

Which means find the range called Plot_Month and go down the number of rows indicated by the contents of cell B1 and right 0 columns. Return to me a range the same number of rows and columns as Plot_Month.

Cell B1 has the formula: =MATCH(A1,Data[Name],0) which finds the row in the data section of the table in column Name on which the value in column A is found.

Plot_Month and Plot_Value are used for the data series and X-axis.

Since they are dynamic, when you add Mar-19, it will become part of the chart automatically.

The full explanation on making named dynamic ranges and how to use them in charts is contained in these two wikis:
http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
http://www.utteraccess.com/wiki/Dynamic_Charting

Note there are spaces around the square brackets in the text. This is to keep the posting software happy. Otherwise it thinks I'm trying to inject malicious code. In the real world these spaces would not exist.

If you want this same flexibility with a pivot table, you would have to "normalize" the data as shown on Sheet 6. Also I needed a "helper column" to convert the string date into a real date.

I see that on sheet 4, you started this attempt and then had to add each month manually. That is because the data is not normalized.

To get the data to display with months on the X-Axis you have to switch row/column which changes the display of the pivot table. In many cases you wind up with two pivot tables: one to display the way you want to see the data in the table and another to run the chart.

If you want to use data in a pivot table efficiently, it is better to "go deep" than to "go wide."

I could have hard coded the 80%, but I wanted to make it dynamic too. Plot_80 =OFFSET(Plot_Month,MATCH("80% Target",Sheet1!$A:$A,0)-3,0)

I will make the assumption that you may want to add or delete rows in the table. Since the 80% line is not part of the table, I have to try something else. So I defined it in terms of where it is found in column A. That is the reason for the -3 in the match formula. Plot_Value is defined as an offset from the table header. Plot_80 is defined as an offset from the first sheet row. Since the table starts on row 3, I had to subtract to get them to align. If you move the table (cut and paste), then you may have to redefine this formula.

One thing you will have to do is "extend" the 80% manually. You can do this in advance, it won't cause any problems other than look funny.

As for normalizing the data. I used Power Query which I am still in the shallow end of the pool with. It is a part of Office 2016 but may be available for other versions as an add-in. I don't really know.

However, I do have a utility that does this very thing. See the attachment.



I added a new series as indicated in the links in my prior post.

I took a look at the pivot table approach. There is no easy way I can think of to add the 80% line. It can be done, but it may be more work than the version you have.