skipping blank cells in dynamic chart

[Ravens Fan]

I am creating a dynamic chart that captures 24 months of data.

I'm using a vlookup to put data into the cells and it goes out 5 years. It
looks like the dynamic chart code picks up the vlookup code 5 years out and
tries to chart it. It also picks up months where no production accured. Is
there away to ignore the vlookup if no data is in the cell?

Here is a sample of my data:

Dec 05 700
Jan 06 725
Feb 06 720
Mar 06 695
Apr 06 710
"blank" "blank"
Jun 06 720
Jul 06 700
"blank" "blank"
Aug 06 710
vlookup code goes out a total of 5 years. So, I have blank cells as of Jan
08 out to 2010. I only want to pickup the last 24 months, where there is data.

I know this is complicated, so, thanks in advance for any help.

 

[Jon Peltier]

Chip Pearson has pages on his web site (http://cpearson.com) that show how
to compress a range with blank rows. Set this up in the next columns in the
sheet and plot from this new range.

- Jon

 

[ShaneDevenshire]

Hi Ravens,

If I understand your question you can use Data, Filter, AutoFilter.

If the "blanks" are really blank then you choose Not Blank from the auto
filter. If the "blanks" contain that word then you use custom filter from
the AutoFilter drop down and choose does not equal and on the right enter
blanks or "blanks" or whatever.

The chart is set by default to plot only visible cells so your chart will
not display the months for which data is missing.

 

[Ravens Fan]

Shane,

Thanks for your advice. It is the easier of the two suggestions. But, I
still have a issue with the dynamic chart. Using the previous data sample I
posted:

Say I'm trying to capture 6 months of data and chart it. Using your idea, it
only charts Apr 06 thru Aug 06 (only 4 months). I need it to go back to Feb
06 thru Aug 06 skipping the 2 blank (empty) cells.

Any suggestions? I'm trying to make this as easy as possibly. I need to
train someone, incase I get hit by a bus. Also, I'm trying to automate as
much as possible. I have 100 charts that track 6 points of data. So, I'm
trying to set it up that the charts update themselves each month.

Thanks in advance for any help.

 

[ShaneDevenshire]

Hi,

Sorry to take so long but I worked a very long day yeasterday. Let's
suppose you are using Excel 2003.

Suppose the data starts in A2 and goes to B60 with titles on row 1, with
dates in column A and values in column B, also assume blank mean nothing in
the cell. Select the entire range A1:B60, and press Ctrl L, then OK. Your
data is a list. Highlight all the data and plot a chart. In cell B1 open
the auto filter and choose Not Blanks, the last choice. Open the auto filter
in cell A1 and choose Top 10, change the number 10 to 6 and click OK. Your
chart should be plotting the last 6 months of data.