I have a linked chart built through the end of the fiscal year based on a
spreadsheet supplied by another office. WITHOUT hiding spreadsheet columns
or deleting cell content or changing chart coordinates, I would like to
instruct the chart not to plot future month's cells with zero values. Is
this possible?

Change "" in your formulas to NA().

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


"DOUG ECKERT" wrote in message
...
I have a linked chart built through the end of the fiscal year based on a
spreadsheet supplied by another office. WITHOUT hiding spreadsheet
columns
or deleting cell content or changing chart coordinates, I would like to
instruct the chart not to plot future month's cells with zero values. Is
this possible?

Jon: Is this in the VBA instructions? I looked there, but could not find it.

DOUG

"Jon Peltier" wrote:

Change "" in your formulas to NA().

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


"DOUG ECKERT" wrote in message
...
I have a linked chart built through the end of the fiscal year based on a
spreadsheet supplied by another office. WITHOUT hiding spreadsheet
columns
or deleting cell content or changing chart coordinates, I would like to
instruct the chart not to plot future month's cells with zero values. Is
this possible?

Jon: I was able to hide the zero plots in the spreadsheets by formatting the
numbers as "0;-0;;@". But, the linked charts still display these datapoints.
Please advise...

"DOUG ECKERT" wrote:

Jon: Is this in the VBA instructions? I looked there, but could not find it.

DOUG

"Jon Peltier" wrote:

Change "" in your formulas to NA().

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


"DOUG ECKERT" wrote in message
...
I have a linked chart built through the end of the fiscal year based on a
spreadsheet supplied by another office. WITHOUT hiding spreadsheet
columns
or deleting cell content or changing chart coordinates, I would like to
instruct the chart not to plot future month's cells with zero values. Is
this possible?

This has nothing to do with VBA; these are purely worksheet formulas. In the
linked cells, you must have a formula like

=A1

or

=IF(ISBLANK(A1),"",A1)

Change these both to something like

=IF(ISBLANK(A1),NA(),A1)

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


"DOUG ECKERT" wrote in message
...
Jon: Is this in the VBA instructions? I looked there, but could not find
it.

DOUG

"Jon Peltier" wrote:

Change "" in your formulas to NA().

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


"DOUG ECKERT" wrote in message
...
I have a linked chart built through the end of the fiscal year based on
a
spreadsheet supplied by another office. WITHOUT hiding spreadsheet
columns
or deleting cell content or changing chart coordinates, I would like to
instruct the chart not to plot future month's cells with zero values.
Is
this possible?

Jon: I do not have a conditional statement in the spreadsheet - I have a
cell reference linked to another workbook. I want to instruct the
spreadsheet to accept the link, but not to display it if it is a zero in a
future month. Using the number formatting command "0;-0;;@", I can hide the
cell content in the spreadsheet, but it still shows up in the graph. I would
like to have the graph treat the cell with the hidden content as a blank
cell. So, for the first eleven months of the fiscal year, I shall display
the linked data, but not the final month (Sep), because it has zeros in it.
I want the linear trend line to do the same thing, i.e., to trend the first
eleven months and to ignore the twelfth for now. The reason for this is that
the underlying data is coming from an outside source and incomplete months
contain zeros in their format. I want to use their data, display it in our
charts, and not have to instruct someone else how to adjust the cell
references each time the data is updated. It should happen automatically, in
other words.

What do you think - is this possible?

"Jon Peltier" wrote:

This has nothing to do with VBA; these are purely worksheet formulas. In the
linked cells, you must have a formula like

=A1

or

=IF(ISBLANK(A1),"",A1)

Change these both to something like

=IF(ISBLANK(A1),NA(),A1)

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


"DOUG ECKERT" wrote in message
...
Jon: Is this in the VBA instructions? I looked there, but could not find
it.

DOUG

"Jon Peltier" wrote:

Change "" in your formulas to NA().

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


"DOUG ECKERT" wrote in message
...
I have a linked chart built through the end of the fiscal year based on
a
spreadsheet supplied by another office. WITHOUT hiding spreadsheet
columns
or deleting cell content or changing chart coordinates, I would like to
instruct the chart not to plot future month's cells with zero values.
Is
this possible?

If the cell contains any kind of formula, it can never be treated as a blank
cell. Hiding the value with a number format does not remove the value from
the cell. You can either change your link formula as I suggested, or define
a dynamic range that knows how many months to plot
(http://peltiertech.com/WordPress/200...namic-charts/), or tolerate
the zero plotting.

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



"DOUG ECKERT" wrote in message
...
Jon: I do not have a conditional statement in the spreadsheet - I have a
cell reference linked to another workbook. I want to instruct the
spreadsheet to accept the link, but not to display it if it is a zero in a
future month. Using the number formatting command "0;-0;;@", I can hide
the
cell content in the spreadsheet, but it still shows up in the graph. I
would
like to have the graph treat the cell with the hidden content as a blank
cell. So, for the first eleven months of the fiscal year, I shall display
the linked data, but not the final month (Sep), because it has zeros in
it.
I want the linear trend line to do the same thing, i.e., to trend the
first
eleven months and to ignore the twelfth for now. The reason for this is
that
the underlying data is coming from an outside source and incomplete months
contain zeros in their format. I want to use their data, display it in
our
charts, and not have to instruct someone else how to adjust the cell
references each time the data is updated. It should happen automatically,
in
other words.

What do you think - is this possible?

"Jon Peltier" wrote:

This has nothing to do with VBA; these are purely worksheet formulas. In
the
linked cells, you must have a formula like

=A1

or

=IF(ISBLANK(A1),"",A1)

Change these both to something like

=IF(ISBLANK(A1),NA(),A1)

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


"DOUG ECKERT" wrote in message
...
Jon: Is this in the VBA instructions? I looked there, but could not
find
it.

DOUG

"Jon Peltier" wrote:

Change "" in your formulas to NA().

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


"DOUG ECKERT" wrote in message
...
I have a linked chart built through the end of the fiscal year based
on
a
spreadsheet supplied by another office. WITHOUT hiding spreadsheet
columns
or deleting cell content or changing chart coordinates, I would like
to
instruct the chart not to plot future month's cells with zero
values.
Is
this possible?

As Jon has already pointed out, replace your cell reference with a
conditional one.

=IF(ISBLANK(cell-reference),NA(),cell-reference)

On Tue, 5 Aug 2008 10:45:01 -0700, DOUG ECKERT
wrote:

Jon: I do not have a conditional statement in the spreadsheet - I
have a
cell reference linked to another workbook. I want to instruct the
spreadsheet to accept the link, but not to display it if it is a zero
in a
future month. Using the number formatting command "0;-0;;@", I can
hide the
cell content in the spreadsheet, but it still shows up in the graph. I
would
like to have the graph treat the cell with the hidden content as a
blank
cell. So, for the first eleven months of the fiscal year, I shall
display
the linked data, but not the final month (Sep), because it has zeros
in it.
I want the linear trend line to do the same thing, i.e., to trend the
first
eleven months and to ignore the twelfth for now. The reason for this
is that
the underlying data is coming from an outside source and incomplete
months
contain zeros in their format. I want to use their data, display it
in our
charts, and not have to instruct someone else how to adjust the cell
references each time the data is updated. It should happen
automatically, in
other words.

What do you think - is this possible?

"Jon Peltier" wrote:

This has nothing to do with VBA; these are purely worksheet formulas. In the
linked cells, you must have a formula like

=A1

or

=IF(ISBLANK(A1),"",A1)

Change these both to something like

=IF(ISBLANK(A1),NA(),A1)

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


"DOUG ECKERT" wrote in message
...
Jon: Is this in the VBA instructions? I looked there, but could not find
it.

DOUG

"Jon Peltier" wrote:

Change "" in your formulas to NA().

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


"DOUG ECKERT" wrote in message
...
I have a linked chart built through the end of the fiscal year based on
a
spreadsheet supplied by another office. WITHOUT hiding spreadsheet
columns
or deleting cell content or changing chart coordinates, I would like to
instruct the chart not to plot future month's cells with zero values.
Is
this possible?







Regards,

Tushar Mehta
Microsoft MVP Excel 2000-2008
www.tushar-mehta.com
Tutorials and add-ins for Excel, PowerPoint, and other products

Jon,

Just jumping in here with a follow up question. Your suggestion worked
perfectly and the chart no longer shows the zero value. The individual cells
however display the characters #N/A. Is there any way to either suppress
that, or get it to display something like "upcoming" or "future"?

Thanks in advance.

"Jon Peltier" wrote:

Change "" in your formulas to NA().

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


"DOUG ECKERT" wrote in message
...
I have a linked chart built through the end of the fiscal year based on a
spreadsheet supplied by another office. WITHOUT hiding spreadsheet
columns
or deleting cell content or changing chart coordinates, I would like to
instruct the chart not to plot future month's cells with zero values. Is
this possible?

If you have anything in the cell besides NA() or #N/A, you will get an
apparent zero value in the chart. You can hide the #N/A error with
conditional formatting:
http://contextures.com/xlCondFormat03.html#Errors

I don't like hiding anything important, I prefer keeping the range as is.
What I often do, though, is keep the chart data range as is for myself, but
insert another sheet with the same data, cleaned up and formatted for a nice
printed report, or an on-screen table. It just takes a few links, and you
didn't have to mess up one data range to try to make it work for two
purposes.

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


"Lin" wrote in message
...
Jon,

Just jumping in here with a follow up question. Your suggestion worked
perfectly and the chart no longer shows the zero value. The individual
cells
however display the characters #N/A. Is there any way to either suppress
that, or get it to display something like "upcoming" or "future"?

Thanks in advance.

"Jon Peltier" wrote:

Change "" in your formulas to NA().

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


"DOUG ECKERT" wrote in message
...
I have a linked chart built through the end of the fiscal year based on
a
spreadsheet supplied by another office. WITHOUT hiding spreadsheet
columns
or deleting cell content or changing chart coordinates, I would like to
instruct the chart not to plot future month's cells with zero values.
Is
this possible?