Automatic formatting of chart lines

[Dave]

Hi
I dont know if anyone can help, but I have a problem I'd
like to resolve for work.
We currently have a database which contains financial
information, which we report on monthly by using Excel to
link into the database.
I have set the spreadsheets up so that when I change the
month number in 1 cell, all the links to the database
update and link to the correct data.
What I want to be able to do is the following:
I have a database of sales figures, which contains 4 months
actual and then 8 months forecasted.
I want to be able to create a simple line graph of these
sales, but with the 4 actual months in a solid line and
then the 8 months forecasts in a dotted line. I can
obviously do this by manually formatting each segement of
the line graph. Next month, I will have 5 months actual
and 7 months forecast. Is there any way I can set the
graph to automatically update the solid and dotted lines to
reflect thed latest month?

Thanks in advance for your help

Dave

 

[dvt]

I have a database of sales figures, which contains 4 months
actual and then 8 months forecasted.
I want to be able to create a simple line graph of these
sales, but with the 4 actual months in a solid line and
then the 8 months forecasts in a dotted line. I can
obviously do this by manually formatting each segement of
the line graph. Next month, I will have 5 months actual
and 7 months forecast. Is there any way I can set the
graph to automatically update the solid and dotted lines to
reflect thed latest month?

I think you can do it with two separate data series. Each series must be
set up as a dynamic series, as described on tushar-mehta.com. One series
will contain the actual, the second series the forecast data.

Now you have to figure out how to tell the series which data is actual and
which is forecast. You could add an extra column with an 'A' for actual or
an 'F' for forecast, then use the COUNTIF function to figure out how many of
each type exist.

I have done this in a very simple spreadsheet, and I will be willing to send
it to you if you wish. Email me privately if you want the spreadsheet.

Dave
dvt at psu dot edu

 

[dvt]

Flaunting NG etiquette, I am responding to my own post. Here is the
spreadsheet in words.

The data is in rows 4-15. Column A is the month, column B contains "a" for
actual or "f" for forecast, and column 3 contains the sales figures. Define
a range called "AF" for B4:B15.

There are four ranges that need to be defined. DatesActual is:
=OFFSET(Sheet1!$A$4,0,0,COUNTIF(AF,"a"))

The second date range is DatesForecast:
=OFFSET(Sheet1!$A$4,COUNTIF(AF,"a"),0,COUNTIF(AF,"f"))

The third range is SalesActual:
=OFFSET(Sheet1!$C$4,0,0,COUNTIF(AF,"a"))

The fourth range is SalesForecast:
=OFFSET(Sheet1!$C$4,COUNTIF(AF,"a"),0,COUNTIF(AF,"f"))

Now create an XY scatter chart. Plot SalesActual vs DatesActual as a solid
line and SalesForecast vs DatesForecast as a dotted line. The sales go in
the Y, dates go in the X.

The offer to send the sheet still stands.

Dave
dvt at psu dot edu