Predetermining graph lines

[Red over White]

I currently have a line graph which has between 20 and 25 lines showing performance over a period. There are times that I have to add lines for short periods and want to be able to specify the thickness of the line as well as predetermine color it should be. I am assuming that I can do the first part by macro, but am wondering how to do the color element - by reference to a cell with that color in. Any suggestions/help would be appreciated, I use Office Mac:2011

 

[Steve=True]

To do this, do the following:

1) You can create a dummy series that only shows a line if data is entered.
=if(isnumber(a1),a1,na()) - NA() will not show up in the chart

or alternately you can choose to show or not

=if(showdata="Yes",a1,na()) - show data is cell in the worksheet that you point to to see if the data line should be shown in the chart.


2) Add the data line for your dummy set and then modify the line size and color as needed. Then when you show the data it will have its own style, but will not show up when not entered or chosen.

You can create as many of these dummy series as you need for different colors or size or whatever you want to show.

Let me know if you need more help.

Steve=True
www.exceldashboardtemplates.com

 

[Red over White]

Steve

Thanks for your help. I can see where you are coming from and it works for the start and end of the line. What I should have made clearer in the original posting is that I was looking to use the same column for two separate entries (say B3:B6 and B9:B14) but it looks as though interpolation will scupper that.

On the second element I have started to develop a macro that reads:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(2).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = D14 'where cell D14 = RGB(0, 0, 255) or red
End Sub

but this doesn't work, but then I would still need to work out how to assign the particular line on the graph to the graph. Am I asking for the impossible? I may not be back until the weekend so don't bust a gut over it
<table style="border-collapse: collapse;width:85pt" border="0" cellpadding="0" cellspacing="0" width="85"><tbody><tr style="height:17.0pt" height="17"><td style="height:17.0pt;width:85pt" height="17" width="85">
</td></tr></tbody></table>

 

[Steve=True]

I don't know macros.

Take a look at Jon's site for a description of what you are trying to do without macros:

http://peltiertech.com/WordPress/easier-interactive-multiple-line-chart/

Steve=True
www.exceldashboardtemplates.com