Automatic Dynamic Chart Ranges

[jwa76]

Hi:

I have an XY plot that refers to a set range of values on my worksheet
In this case, it is 8 rows long. I chose 8 rows because that is th
maximum number of data points that I will ever need. I have set up th
chart values so that they are dynamically calculated based on VLOOKUP
from other data. Sometimes there are only 2 data points, sometimes
or 6. The problem is that no matter how I set up the formulas, Exce
does not like the cells without values in them being included in th
chart series (I have hardwired it to include all 8 rows). I thought i
might ignore a blank ("" in the formula), but it does not, and adjust
the whole series to a "line" series (integral values of 1, 2,
regardless of my X data). First of all, is there any way around thi
issue directly, i.e. can I put a result in the cells which don't hav
data that Excel will ignore? Of course I could erase the cell
manually or set up a macro, but I am doing this on hundreds o
different worksheets.

I have also considered dynamic ranges, but I can't find any solutio
which does not involve creating named ranges, which will be ver
difficult with all the different worksheets I must create. Apparentl
Excel does not allow much in the "SERIES" keyword for its charts -
have tried INDIRECT, OFFSET, etc., without any success.

I have checked this forum (albeit somewhat superficially, as I am no
quite sure what to search for) and have not found any threads relatin
to this - if there are some, please point them out to me.

Thanks for any assistance,

John

 

[Bernard Liengme]

Two possible items of use to you:
i) Example of dynamic chart at www.stfx.ca/people/bliengme/ExcelTips
ii) Rather that using spaces (as in " ") use N/A (as in NA()) and Excel will
ignore when charting

best wishes

 

[Raeven]

Ah, I see. This is a very good tip. I used the NA() for some of m
data, but there is one set of data that I use for several charts, and
want all the data to appear on some charts, but not on others.

Basically, I have it set up like this:

=IF(TODAY()>('Fiscal Calendar'!B11+9),R274/R$299,NA())

where Fiscal Calendar'!B11+9 is the date whereafter I want the data t
appear on the chart, and this is the formula for the *actual
man/hours. In my chart, I compare this data to the man/hour
available. In this case, I don't want available data to appear afte
the current fiscal month. Thus, the NA() formula would work.

However, when I compare the man/hours available to the man/hour
scheduled, I want data to appear until April 06. I am using the sam
source data for available man/hours for both charts. Is there any wa
to get the available man/hours data to appear on th
available-scheduled chart, but not on the available-actual chart?

Thanks a billion!

Stephani