Charts: Using data from different sheets in 1 data series

Beginner Bob · Mar 4th, 2002, 02:05 PM

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 · Mar 4th, 2002, 02:13 PM

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 · Mar 4th, 2002, 02:53 PM

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 · Mar 4th, 2002, 03:25 PM

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 · Mar 5th, 2002, 01:34 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 4th, 2002, 02:05 PM	#1
Beginner Bob New Member Join Date: Feb 2002 Location: San Diego Posts: 14	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.

Mar 4th, 2002, 02:13 PM	#2
jeffmfrank Board Regular Join Date: Feb 2002 Location: Arizona Posts: 72	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)?

Mar 4th, 2002, 02:53 PM	#3
Beginner Bob New Member Join Date: Feb 2002 Location: San Diego Posts: 14	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.

Mar 4th, 2002, 03:25 PM	#4
jeffmfrank Board Regular Join Date: Feb 2002 Location: Arizona Posts: 72	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.

Mar 5th, 2002, 01:34 PM	#5
Beginner Bob New Member Join Date: Feb 2002 Location: San Diego Posts: 14	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.