Dynamic Charts and Blanks

Dear All.

I have produced a chart dynamically, using the technique outlined on the
Peliter website.

In general this works fine, in terms of when the list of data grows.
However, when my data list shrinks it causes a problem.

The data is the result of an Offset function, which leaves a blank cell
where there is no data. However, Excel charts a zero value - which I dont
want to have (I would rather it treat the cells as though they were blank).

Does anyone know how to do this???

Thanks.

You need to change your formula so it results in NA() instead of "" (which
isn't blank, by the way, it's a short text value). This looks like #N/A in
the sheet, but isn't plotted in a line or XY chart. Use conditional
formatting to hide the error:

http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"BoRed79" wrote in message
...
Dear All.

I have produced a chart dynamically, using the technique outlined on the
Peliter website.

In general this works fine, in terms of when the list of data grows.
However, when my data list shrinks it causes a problem.

The data is the result of an Offset function, which leaves a blank cell
where there is no data. However, Excel charts a zero value - which I dont
want to have (I would rather it treat the cells as though they were
blank).

Does anyone know how to do this???

Thanks.

Thanks.

How do I change my formula to get an error??

My current formula is:

=OFFSET($BL$1,MATCH(ROW()-1,$BL$2:$BL$17,0),-2)

"Jon Peltier" wrote:

You need to change your formula so it results in NA() instead of "" (which
isn't blank, by the way, it's a short text value). This looks like #N/A in
the sheet, but isn't plotted in a line or XY chart. Use conditional
formatting to hide the error:

http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"BoRed79" wrote in message
...
Dear All.

I have produced a chart dynamically, using the technique outlined on the
Peliter website.

In general this works fine, in terms of when the list of data grows.
However, when my data list shrinks it causes a problem.

The data is the result of an Offset function, which leaves a blank cell
where there is no data. However, Excel charts a zero value - which I dont
want to have (I would rather it treat the cells as though they were
blank).

Does anyone know how to do this???

Thanks.

I'm guessing that it might be like this, though I'm not sure I understand
why it doesn't already raise an error:

=if(isna(MATCH(ROW()-1,$BL$2:$BL$17,0)),NA(),OFFSET($BL$1,MATCH(ROW()-1,$BL$2:$BL$17,0),-2))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"BoRed79" wrote in message
...
Thanks.

How do I change my formula to get an error??

My current formula is:

=OFFSET($BL$1,MATCH(ROW()-1,$BL$2:$BL$17,0),-2)

"Jon Peltier" wrote:

You need to change your formula so it results in NA() instead of ""
(which
isn't blank, by the way, it's a short text value). This looks like #N/A
in
the sheet, but isn't plotted in a line or XY chart. Use conditional
formatting to hide the error:

http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"BoRed79" wrote in message
...
Dear All.

I have produced a chart dynamically, using the technique outlined on
the
Peliter website.

In general this works fine, in terms of when the list of data grows.
However, when my data list shrinks it causes a problem.

The data is the result of an Offset function, which leaves a blank cell
where there is no data. However, Excel charts a zero value - which I
dont
want to have (I would rather it treat the cells as though they were
blank).

Does anyone know how to do this???

Thanks.