Hide future month's linked cells with value of zero

[DOUG ECKERT]

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 Peltier]

Change "" in your formulas to NA().

- Jon

 

[DOUG ECKERT]

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

DOUG

 

[DOUG ECKERT]

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...

 

[Jon Peltier]

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

 

[DOUG ECKERT]

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]

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/2008/05/14/dynamic-charts/), or tolerate
the zero plotting.

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

 

[Tushar Mehta]

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

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?

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
_______

Regards,

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

 

[Lin]

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]

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