I have a line chart with weekly data for 2 years. Weeks on the X axis and
Pounds on the Y axis. They represents actual and forecasted pounds. I need to
show the first portion of the line (actual) with solid color while the rest
of the line (forecast) with dotted line. We are talking about one single line
for production. How can I accomplish this? I don't want to do it manually.
I'd rather have it changes automatically every week when the actual data
override the forecasted data.

Thanks.

sahafi

Hi there!

To accomplish this, you can use a combination of Excel's chart formatting options and a dynamic named range. Here are the steps:

1. First, create a dynamic named range for your data. This will allow your chart to automatically update as new data is added. To do this, select your data range (including headers) and go to Formulas Define Name. In the "New Name" dialog box, give your range a name (e.g. "DataRange") and in the "Refers to" field, enter the formula
   Formula:

   "=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2)" 

   . This formula will automatically adjust the range based on the number of rows of data you have.
   
2. Create a line chart using your named range as the data source. To do this, select your data range and go to Insert Charts Line Chart. Excel will automatically create a line chart with your data.
   
3. Right-click on the chart and select "Select Data". In the "Select Data Source" dialog box, click on the "Edit" button next to the "Legend Entries (Series)" box.
   
4. In the "Edit Series" dialog box, select the series you want to format (in this case, the entire line) and click on the "Format" button.
   
5. In the "Format Data Series" dialog box, go to the "Line" tab and select a solid line style for the first portion of the line (actual data). Then, go to the "Marker" tab and select "None" for the marker options.
   
6. Click "OK" to close the "Format Data Series" dialog box, then click "OK" again to close the "Edit Series" dialog box.
   
7. Right-click on the chart again and select "Select Data". In the "Select Data Source" dialog box, click on the "Add" button next to the "Legend Entries (Series)" box.
   
8. In the "Edit Series" dialog box, enter a name for the new series (e.g. "Forecast"), then select the range of forecasted data (excluding the actual data) using the "Series values" box. Click "OK" to close the dialog box.
   
9. In the "Select Data Source" dialog box, select the new "Forecast" series and click on the "Format" button.
   
10. In the "Format Data Series" dialog box, go to the "Line" tab and select a dotted line style for the forecasted data. Then, go to the "Marker" tab and select "None" for the marker options.
    
11. Click "OK" to close the "Format Data Series" dialog box, then click "OK" again to close the "Select Data Source" dialog box.

Your chart should now display the actual data with a solid line and the forecasted data with a dotted line. As you add new data, the chart will automatically update to reflect the new actual data and adjust the formatting accordingly.

Make the chart with two series. Put all the dates in column A, put the
actuals in column B down to some date with blanks below, put the forecast in
column C from that date downward with blanks above. Plot this data, format
the two series the way you want them to appear (one solid, one dashed). As
you add an actual value, delete a forecast value. The chart keeps up
automatically.

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


"sahafi" wrote in message
...
I have a line chart with weekly data for 2 years. Weeks on the X axis and
Pounds on the Y axis. They represents actual and forecasted pounds. I need
to
show the first portion of the line (actual) with solid color while the
rest
of the line (forecast) with dotted line. We are talking about one single
line
for production. How can I accomplish this? I don't want to do it manually.
I'd rather have it changes automatically every week when the actual data
override the forecasted data.

Thanks.

sahafi

Thanks Jon. That trick worked. But one thing, how can I connect the two lines
together? my actual goes down to week 12 and my forecast starts on week 13.
On the graph there's space between the points 12 and 13. I have tried to move
the forecst data up one row to start at 12, but that didn't help either.

Thanks.

"Jon Peltier" wrote:

Make the chart with two series. Put all the dates in column A, put the
actuals in column B down to some date with blanks below, put the forecast in
column C from that date downward with blanks above. Plot this data, format
the two series the way you want them to appear (one solid, one dashed). As
you add an actual value, delete a forecast value. The chart keeps up
automatically.

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


"sahafi" wrote in message
...
I have a line chart with weekly data for 2 years. Weeks on the X axis and
Pounds on the Y axis. They represents actual and forecasted pounds. I need
to
show the first portion of the line (actual) with solid color while the
rest
of the line (forecast) with dotted line. We are talking about one single
line
for production. How can I accomplish this? I don't want to do it manually.
I'd rather have it changes automatically every week when the actual data
override the forecasted data.

Thanks.

sahafi

At the X value where the curves meet, include a Y value for both series.

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


"sahafi" wrote in message
...
Thanks Jon. That trick worked. But one thing, how can I connect the two
lines
together? my actual goes down to week 12 and my forecast starts on week
13.
On the graph there's space between the points 12 and 13. I have tried to
move
the forecst data up one row to start at 12, but that didn't help either.

Thanks.

"Jon Peltier" wrote:

Make the chart with two series. Put all the dates in column A, put the
actuals in column B down to some date with blanks below, put the forecast
in
column C from that date downward with blanks above. Plot this data,
format
the two series the way you want them to appear (one solid, one dashed).
As
you add an actual value, delete a forecast value. The chart keeps up
automatically.

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


"sahafi" wrote in message
...
I have a line chart with weekly data for 2 years. Weeks on the X axis
and
Pounds on the Y axis. They represents actual and forecasted pounds. I
need
to
show the first portion of the line (actual) with solid color while the
rest
of the line (forecast) with dotted line. We are talking about one
single
line
for production. How can I accomplish this? I don't want to do it
manually.
I'd rather have it changes automatically every week when the actual
data
override the forecasted data.

Thanks.

sahafi

Adding a 'Y' value to the forecast data only worked better than adding the
same value to both actual/fcst.

Thanks for the help.
--
when u change the way u look @ things, the things u look at change.


"Jon Peltier" wrote:

At the X value where the curves meet, include a Y value for both series.

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


"sahafi" wrote in message
...
Thanks Jon. That trick worked. But one thing, how can I connect the two
lines
together? my actual goes down to week 12 and my forecast starts on week
13.
On the graph there's space between the points 12 and 13. I have tried to
move
the forecst data up one row to start at 12, but that didn't help either.

Thanks.

"Jon Peltier" wrote:

Make the chart with two series. Put all the dates in column A, put the
actuals in column B down to some date with blanks below, put the forecast
in
column C from that date downward with blanks above. Plot this data,
format
the two series the way you want them to appear (one solid, one dashed).
As
you add an actual value, delete a forecast value. The chart keeps up
automatically.

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


"sahafi" wrote in message
...
I have a line chart with weekly data for 2 years. Weeks on the X axis
and
Pounds on the Y axis. They represents actual and forecasted pounds. I
need
to
show the first portion of the line (actual) with solid color while the
rest
of the line (forecast) with dotted line. We are talking about one
single
line
for production. How can I accomplish this? I don't want to do it
manually.
I'd rather have it changes automatically every week when the actual
data
override the forecasted data.

Thanks.

sahafi

Hello,

I am doing something similar to what Sahafi is doing except I am doing monthly budget data. My chart data looks as follows...

Col A Col B Col C
Oct 1000
Nov 3000 3000
Dec 4000
Jan 5000
Feb 6000
Mar 7000
Apr 8000
Jun 9000
Jul 10000
Aug 11000
Sept 12000

The numeric data is gotten via paste links to cells in a different sheet within the same workbook that use VB functions to calculate the cell values. However, when I go to plot the data, my x-axis for the months is cut short and I do not see a line for the data in column B. Any ideas what might be going on?

Your X axis is "cut short" how? I simply selected the data and created a
chart, and I got months Oct thru Sept (except for May, missing in your
data), with one line from Oct to Nov and the other from Nov to Sept.

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


<John Scola wrote in message ...
Hello,

I am doing something similar to what Sahafi is doing except I am doing
monthly budget data. My chart data looks as follows...

Col A Col B Col C
Oct 1000
Nov 3000 3000
Dec 4000
Jan 5000
Feb 6000
Mar 7000
Apr 8000
Jun 9000
Jul 10000
Aug 11000
Sept 12000

The numeric data is gotten via paste links to cells in a different sheet
within the same workbook that use VB functions to calculate the cell
values. However, when I go to plot the data, my x-axis for the months is
cut short and I do not see a line for the data in column B. Any ideas
what might be going on?