Create a Chart w/data from multiple worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an excel file that has the following worksheets.

Jan 4
Feb 5
March 6
June 7
July 6

They each have data in the cell G15. I need the data from each of those
cells plotted on the same chart. We've been using Lotus and it was super
simple. I'm at a loss how to do the same thing in Excel.

Thanks to anyone who can answer this question!!
 
I've tried this and I'm still not getting the result I need. Here's what I
used for the formula:

=SERIES(Jan 4!$A$15:$G$15, Feb 5!$A$15:$G$15, March 6$A$15:$G$15, June
7$A$15:$G$15, July 5$A$15:$G$15)

Is there a simpler way to get all the sheets?? In Lotus, I could just type
Jan 4:G15...July5:G15....Then all worksheets between those would be graphed
 
Let me answer your question by quoting from the article I cited:

"Each of the three elements that can be linked to worksheet values (Series
Name, X Values, and Y Values) can only come from a single sheet."

Excel does not offer the same feature that you have found in Lotus 123. (I
know nothing of Lotus 123, having used it only briefly almost 15 years ago
before ditching it in favor of Excel.) You have to set up a summary range on
one worksheet in order to get data from multiple sheets into a single
component of the SERIES formula.

- Jon
 
I've tried this and I'm still not getting the result I need. Here's what I
used for the formula:

=SERIES(Jan 4!$A$15:$G$15, Feb 5!$A$15:$G$15, March 6$A$15:$G$15, June
7$A$15:$G$15, July 5$A$15:$G$15)

Is there a simpler way to get all the sheets?? In Lotus, I could just type
Jan 4:G15...July5:G15....Then all worksheets between those would be graphed
on chart. Does Excel have something similar???
Click to expand...

Lotus 123 was in many ways superior to Excel even today. One of those
ways was that it had genuine 3D spreadsheet capability that Excel only
ever faked clumsily by having separate 2D spreadsheets.

I suggest that, as Jon says, you create a separate summary sheet, and in
that sheet create a function that uses INDIRECT to read the contents of
cells that you populate, and go to the cell indicated by those cells.
For instance

=INDIRECT("'"&"Jan 4"&"!A15"&"'")

will return the contents of cell 'Jan 4!A15', by concatenating the text
values "'", "Jan 4", "!A15" and "'". I hope you can easily see how to
build a 2D table that has "Jan 4", "!A15", as row and column headings.
You can then create a chart from that table.

(By the way, all the palaver with " ' " is made necessary by your use of
spaces in the sheet names. Eliminate the spaces and you should be able
to get away with not using them)
 
Back
Top