I need to prevent graph from displaying future months with no sales data

[mvparker79]

I have this spreadsheet that has 2014 and 2013 sales data which I graph as a bar chart and calculate organic growth. I'm trying to make this 100% automated by using formulas in my chart's data source. I have it set up so that the 2013 data does not populate until 2014 data is entered for the new month. The problem is that my boss doesn't want to see month's with no data. So in the sample spreadsheet that I posted he just wants to see a graph with data for January through August. I've tried setting the value to #N/A, "#N/A", NA() and "na" thinking that it would not plot error data. I even tried setting the months to the same thing thinking that the axis wouldn't plot error data. Surely there is a simple way to automate this without the need for a macro. If anyone has any ideas I sure would be grateful.

[Andy Pope]

search forum for dynamic named range examples.

Use of NA will only suppress data markers and shorten lines. To remove categories you need to use named ranges as the source for each data series.

[mvparker79]

I've been looking over and playing with dynamic named ranges and I'm still not getting a solution. The most I'm able to do is get my chart to not include the month on the axis, yet the axis categories is just blank in those out months. This is because my 2013 data is not truly blank. It contains a formula that tells it to appear blank until 2014 sales are entered. The only way to get it to work is if I delete the 2013 data. That will not work as 2013 sales are hard coded and this needs to be a one step process. Here is the workbook I've got so far with the named ranges. I've got a graph on sheet1 that works, but only if series1 and series2 are blank.

[FDibbins]

Another (cheap and nasty) way would be to hide the columns for the months you have not yet got to

[mvparker79]

Right. I thought about that, but this is to be used by the president of the company and I need this to be as dummy proof as possible.

[mvparker79]

Does anyone have any ideas?

[mvparker79]

I still really need some help on this if anyone is willing.

[FDibbins]

Not sure if this will be any better than what you have, but try this.

In A3 enter 1/1/2014 and format as Custom Format MMMM
In A4, copied down...
=IF(B4="","",EDATE(A3,1))
formatted Custom MMMM

[mvparker79]

Thank you but that also didn't work because there are formulas in columns B and D. When I remove those formulas it works just fine. But then it is not automated.

[mvparker79]

I JUST FIGURED IT OUT!!!

The dynamic named range was close but not 100%. IT should have been:

=OFFSET(Graph!$A$3,0,0,COUNTIF(Graph!$A:$A,">0"))

Countif greater than 0! I'm so excited right now. Thanks for your help!

[David Myers]

I found an excellent article called Dynamic Charts that showed me exactly how to do this. http://peltiertech.com/WordPress/dynamic-charts/

[FDibbins]

Im glad you got your question resolved, and thanks for sharing the answer and that link with us