Removing blank values in a dynamic chart

[rob78]

Hi All,


I really liked the posts on dynamic chart and it has helped a semi-literate XL user to generate a dynamic graph. I have managd to get mutiple data on one graph and also remove data once another cell is populated however I am struggling with one thing.


My x-axis is cannot be populated in bulk like days of the week or months and is as variable as the y-axis. With this in mind, I have left it blank, but containing a formula. This is ok but on the graph there are still data points left.


I am creating this for work and need it to be usable for non-XL literates so I need to column to be popluated so it can take the data and manipulate it once it has been added.


I have heard that COUNTIF is an alternative to COUNTA but I cannot get it to work with my formula which was taken from another website (peltiertech.com/Excel/Charts/DynamicColumnChart1.html).


Y-axis =OFFSET(Sheet1!$B$1,1,0,COUNTIF(Sheet1!$B:$B,">0"),1)

X-axis =OFFSET(Sheet1!ChartValues,0,1)


Any help for much appreciated


Rob

 

[jeremymjp]

The function NA() returns a value that the chart won't show. So, I don't know exactly how you're implementing what you've got going on, but if you can check ISBLANK() to be TRUE, then run NA(), your graph won't show the value.

 

[NARAYANK991]

Hi Rob ,


The link you have given mentions the following :


QUOTE


In the Names in Workbook box, type a one-word name for the range, starting with the SheetName, e.g. Sheet1!ChartValues


Note for Excel 2007 users: This technique will fail if your Names begin with the word "Chart". Use a name like ChtValues instead.


UNQUOTE


Does this apply in your case ?


Narayan