Charts: Using data from different sheets in 1 data series

Beginner Bob

New Member
Joined
Feb 21, 2002
Messages
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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)?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top