Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Lee IT
 
Posts: n/a
Default How do I remove empty chart plots from linked worksheet charts

Using Excel 2003, I have a workbook with a master worksheet and an associated
line chart, also several derivative worksheets with associated individual
line charts. The other worksheets are all linked to their respective columns
on the master sheet so that as the master is periodically updated, the others
update automatically.
The problem is that currently empty cells on the master sheet are displayed
as zero's on the linked worksheets and subsequently plotted on their
individual respective charts. The zero's can be removed from both the linked
worksheets and their respective charts, but the empty plots remain.
The charts contain negative and positive values, so where the chart should
stop (no entry), as does the master chart, it in fact returns to zero
throughout the rest of the chart. Zero IS a valid entry IF it has been
entered on the master sheet.
  #2   Report Post  
Andy Pope
 
Posts: n/a
Default

Hi,

Use NA() in your formulas. So something like,

=IF(A1="",NA(),A1)

Cheers
Andy

Lee IT wrote:
Using Excel 2003, I have a workbook with a master worksheet and an associated
line chart, also several derivative worksheets with associated individual
line charts. The other worksheets are all linked to their respective columns
on the master sheet so that as the master is periodically updated, the others
update automatically.
The problem is that currently empty cells on the master sheet are displayed
as zero's on the linked worksheets and subsequently plotted on their
individual respective charts. The zero's can be removed from both the linked
worksheets and their respective charts, but the empty plots remain.
The charts contain negative and positive values, so where the chart should
stop (no entry), as does the master chart, it in fact returns to zero
throughout the rest of the chart. Zero IS a valid entry IF it has been
entered on the master sheet.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
  #3   Report Post  
Lee IT
 
Posts: n/a
Default

Hi again,

That solution was what I was looking for, thanks. However, I now have a new
problem.
I am creating a similar chart and getting the same problem, but with a
Divide by zero error. I have 3 tables, a formula in cells on the the third
divides values in the 1st and 2nd. Those cells that have no data as yet
return #DIV/0! in the 3rd which subsequently plot as zero on the chart (which
is dependant on table3).

The current formula is: =SUM(I29/(C29/8.75))

"Andy Pope" wrote:

Hi,

Use NA() in your formulas. So something like,

=IF(A1="",NA(),A1)

Cheers
Andy

Lee IT wrote:
Using Excel 2003, I have a workbook with a master worksheet and an associated
line chart, also several derivative worksheets with associated individual
line charts. The other worksheets are all linked to their respective columns
on the master sheet so that as the master is periodically updated, the others
update automatically.
The problem is that currently empty cells on the master sheet are displayed
as zero's on the linked worksheets and subsequently plotted on their
individual respective charts. The zero's can be removed from both the linked
worksheets and their respective charts, but the empty plots remain.
The charts contain negative and positive values, so where the chart should
stop (no entry), as does the master chart, it in fact returns to zero
throughout the rest of the chart. Zero IS a valid entry IF it has been
entered on the master sheet.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

  #4   Report Post  
Andy Pope
 
Posts: n/a
Default

Hi,

One was is to check that c29 is not zero,
=IF(C29<0,SUM(I29/(C29/8.75)),NA())

Cheers
Andy

Lee IT wrote:
Hi again,

That solution was what I was looking for, thanks. However, I now have a new
problem.
I am creating a similar chart and getting the same problem, but with a
Divide by zero error. I have 3 tables, a formula in cells on the the third
divides values in the 1st and 2nd. Those cells that have no data as yet
return #DIV/0! in the 3rd which subsequently plot as zero on the chart (which
is dependant on table3).

The current formula is: =SUM(I29/(C29/8.75))

"Andy Pope" wrote:


Hi,

Use NA() in your formulas. So something like,

=IF(A1="",NA(),A1)

Cheers
Andy

Lee IT wrote:

Using Excel 2003, I have a workbook with a master worksheet and an associated
line chart, also several derivative worksheets with associated individual
line charts. The other worksheets are all linked to their respective columns
on the master sheet so that as the master is periodically updated, the others
update automatically.
The problem is that currently empty cells on the master sheet are displayed
as zero's on the linked worksheets and subsequently plotted on their
individual respective charts. The zero's can be removed from both the linked
worksheets and their respective charts, but the empty plots remain.
The charts contain negative and positive values, so where the chart should
stop (no entry), as does the master chart, it in fact returns to zero
throughout the rest of the chart. Zero IS a valid entry IF it has been
entered on the master sheet.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
linked values in a chart Bill H. Charts and Charting in Excel 2 January 2nd 05 06:29 AM
Problem with xlusrgal.xls file Alfred S C Lee Charts and Charting in Excel 2 December 29th 04 06:54 PM
Impedding/Overlaying Charts Phil Hageman Charts and Charting in Excel 4 December 17th 04 08:25 PM
create space in line chart between points, linked to pivot table Mike -Z- Charts and Charting in Excel 1 December 7th 04 10:39 PM


All times are GMT +1. The time now is 03:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"