Chart not ignoring error values/blanks in XL2007

[meami111]

Hi, would be thankful if you can let me know how a chart will ignore the error values. I have a chart based on data range from A2:E12. The column headings are Month, Salesperson, Product, Quantity and Value. As per formula given, the data for these cells can either be a number, error or blank. When I make a bar chart based on this data range, I find #NA or blank featuring in the horizontal axis. How to build a chart which will ignore the error/blank values?

 

[tcmaes]

Hi, thank you for the video, it is very informative. But I have 1 question, I would like to take this a step further. I have used this method above to help create dynamic ranges in what I call my "template worksheet". I am than running a macro to create additional worksheets from that template and produce several plots using different dynamic data. Where I run into the problem is on the graphs. Excel is creating additional names in the name manager, and the scope seems correct; but the graphs are not producing dynamic range results. Any tips? 

 

[rossa]

I can't watch Mike's video with sound right now but does it cover a case where your chart area is determined by a formula? More specifically where the size of the data area will vary, say you have three products and they are presented in the columns and you want to see it from the point of vierw of customers.
Now the number of customers can vary depending on the formula. So in some cases the data table will contain three customers and in another four.
It's easy enough to build a table based on formulas but when the table only contains three customers the chart still looks like it has four, but there is a large blank as one whole row of the data table is blank.

 

[tcmaes]

Yes it does cover dynamic ranges.