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