Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Charts: Using data from different sheets in 1 data series

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    San Diego
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Arizona
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)?

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    San Diego
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Arizona
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    San Diego
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •