How do I hide part of a data series in a chart?

We have a chart with 2 data series - Budget & Actual. We want to show all 12
months of the budget, but only the year-to-date months of the actual. The
problem is the data cells for the future actual amounts have formulas in them
so the chart use the result and plots the line. We do not want to delete the
data (which will require manual input each month, we just want the actual
data series line to not show for the future periods. We tried using IF
statements with 0, "", " " and an alpha character, but these have not worked.
--
Jeff

Hi Jeff:
You can set up your actual data as a dynamic named range series, using the
OFFSET function. Many MVPs give tutorials how to do this on their websites,
see for instance:
http://www.tushar-mehta.com/excel/ne...rts/index.html
http://www.stfx.ca/people/bliengme/e...ps.htm#Dynamic
http://peltiertech.com/Excel/Charts/Dynamics.html
http://www.contextures.com/xlNames02.html

GL,
Henk



"Jeff Bremer" wrote:

> We have a chart with 2 data series - Budget & Actual. We want to show all 12
> months of the budget, but only the year-to-date months of the actual. The
> problem is the data cells for the future actual amounts have formulas in them
> so the chart use the result and plots the line. We do not want to delete the
> data (which will require manual input each month, we just want the actual
> data series line to not show for the future periods. We tried using IF
> statements with 0, "", " " and an alpha character, but these have not worked.
> --
> Jeff