Charts: Using data from different sheets in 1 data series

[Beginner Bob]

I've been trying to create a chart using info from three different sheets named "2000, 2001, and 2002". I can't figure out how to use cells E8:E59 on each for 1 data series. Using them in 3 data series' is no problem, having them overlap each other, but I want to have them as a 3 year trend line butting up against each other, but since it's on 3 different sheets, I can't figure it out. I've tried:

='2000'!$E$8:$E59+'2001'!$E$8:$E59+'2002'!$E$8:$E59

which I saw somewhere, for the data series and data range, but it gives me an error message. Is this even possible.

 

[jeffmfrank]

You could put them into 3 separate series and just format them the same so they appear as one continuous series. Is there a reason you need them in one series (like you are applying a trendline)?

 

[Beginner Bob]

Jeff, what do I need to format to get 1 series? Yes, I need a 3 year trendline, with no gaps in between, so that December 2000 rolls right into January of 2001, etc. Or in other words, I want the series to go from the last cell in 2000 (E59), straight to the first cell in 2001 (E8), and the same for the next year. If I put them in 3 different series', they overlap each other on the chart as a comparison, which isn't what I need. Thanks.

 

[jeffmfrank]

Make the chart type an XY scatterplot. Make sure that each value you're tracking has a cooresponding date value. Then plot the 3 series distincly defining the X values as the dates for each. Then you can change the formatting of each series line to one color to make it appear as continuous. Let me know if that helps.

 

[Beginner Bob]

That works great! One more picky request. Is there ANY way of connecting the lines of the series' together, either by the smoothed line, or adding a trendline for all 3 series treating them as 1? If not, oh well, but that would be perfect. Thanks for all of your help.