+ Reply to Thread
Results 1 to 4 of 4

How do I remove empty chart plots from linked worksheet charts

  1. #1
    Lee IT
    Guest

    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. #2
    Andy Pope
    Guest

    Re: How do I remove empty chart plots from linked worksheet charts

    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. #3
    Lee IT
    Guest

    Re: How do I remove empty chart plots from linked worksheet charts

    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. #4
    Andy Pope
    Guest

    Re: How do I remove empty chart plots from linked worksheet charts

    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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1