title: Offset function for dynamic range not ignoring cells with #N/A in Line Chart
body: <p>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).</p>
  
  <p>Below is the code:</p>
  
  <p>Formula to pull the products from the pivot:</p>
  
  <p>IFERROR(INDEX($A$10:$A$155, AGGREGATE(15,3,ROW($A$10:$A$155)-ROW($A$9)/($A$10:$A$155&lt;>""),ROWS($A$10:A10))),#N/A) </p>
  
  <p>Formula to display the corresponding blended price for the product: </p>
  
  <p>IFERROR(INDEX(AZ$10:AZ$155, MATCH($CC10,$A$10:$A$155,0)),#N/A)</p>
  
  <p>Now I need to create a line chart for the above data. Below is the code for Name Manager:</p>
  
  <p>Quarters=OFFSET('Blended Price Calculation'!$CD$9,0,0,1,COUNTA('Blended Price Calculation'!$CC$9:$DA$9)-1)</p>
  
  <p>0.45_Saline=OFFSET('Blended Price Calculation'!$CD$10,0,0,1,COUNTA('Blended Price Calculation'!$CC$10:$DA$10)-1)</p>
  
  <p>0.9_Saline=OFFSET('Blended Price Calculation'!$CD$11,0,0,1,COUNTA('Blended Price Calculation'!$CC$11:$DA$11)-1)</p>
  
  <p>10_Dextrose=OFFSET('Blended Price Calculation'!$CD$12,0,0,1,COUNTA('Blended Price Calculation'!$CC$12:$DA$12)-1)</p>
  
  <p>Similarly for the rest of the products</p>
  
  <p>I want to the horizontal axis to be Quarters and not Products.</p>
  
  <p>Dashboard - Filter based on Slicer
  <img src="https://i.stack.imgur.com/FQtst.png" alt="Dashboard"></p>
  
  <p>Original Excel without filter
  <img src="https://i.stack.imgur.com/PRmoe.png" alt="Excel 1"></p>
  
  <p>Excel when Slicer filter is applied (to display only 2 products)
  <img src="https://i.stack.imgur.com/HOrve.png" alt="Excel"></p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Any help is really appreciated! Thank you!</p>