Offset function for dynamic range not ignoring cells with #N/A in Line Chart

Question

I am facing an issue with excel offset function for a range which increases horizontally and vertically. I have a pivot table built from raw data which keeps on updating. I am mainly concerned with products and corresponding price per quarter in the pivot. I have created another sheet pulling the products from the pivot and corresponding blended price (calculated based on pivot values - Sales/Units).

Below is the code:

Formula to pull the products from the pivot:

IFERROR(INDEX($A$10:$A$155, AGGREGATE(15,3,ROW($A$10:$A$155)-ROW($A$9)/($A$10:$A$155<>""),ROWS($A$10:A10))),#N/A)

Formula to display the corresponding blended price for the product:

IFERROR(INDEX(AZ$10:AZ$155, MATCH($CC10,$A$10:$A$155,0)),#N/A)

Now I need to create a line chart for the above data. Below is the code for Name Manager:

Quarters=OFFSET('Blended Price Calculation'!$CD$9,0,0,1,COUNTA('Blended Price Calculation'!$CC$9:$DA$9)-1)

0.45_Saline=OFFSET('Blended Price Calculation'!$CD$10,0,0,1,COUNTA('Blended Price Calculation'!$CC$10:$DA$10)-1)

0.9_Saline=OFFSET('Blended Price Calculation'!$CD$11,0,0,1,COUNTA('Blended Price Calculation'!$CC$11:$DA$11)-1)

10_Dextrose=OFFSET('Blended Price Calculation'!$CD$12,0,0,1,COUNTA('Blended Price Calculation'!$CC$12:$DA$12)-1)

Similarly for the rest of the products

I want to the horizontal axis to be Quarters and not Products.

Dashboard - Filter based on Slicer

Original Excel without filter

Excel when Slicer filter is applied (to display only 2 products)

Problem: The chart displays #N/A when I filter data using Slicer in the dashboard. Slicer is acting on pivot table. Basically I need help on offset function which will pick only those values which has data for both rows and columns. Currently I have the formula working for Columns but it is not working for rows, hence the line chart is considering all the rows. But since I have selected only a couple of products (using slicer) out of 100 odd products, line chart shows graph perfectly for those selected products and shows #N/A for the rest.

How can I not display #N/A in my chart legend? I think having a combination of vertical and horizontal offset function should solve my problem but I am struggling to find the solution.

Any help is really appreciated! Thank you!

Answer 1

The issue here is that you have hard-wired ten series into the chart. You can use range names to dynamically change the number of data points in a series, but you can't use a formula approach to dynamically change the number of series in a regular Excel chart. That will require VBA.

If you use a pivot chart instead, you can select the series with a slicer and the legend will show only the visible series.

Edit: Here is a screenshot to illustrate how a pivot chart can be filtered and unused series will not show in the legend.