Books at Amazon.com
Charting Data From Different Sheets
A more detailed description of a chart and its series formula is presented in the The Chart Series Formula page elsewhere on this site, but a brief description here is in order.
Every chart series has a formula which describes the data in the series. For example, a simple series formula looks like this:
This formula can be broken up into four elements as follows:
=SERIES([Series Name],[X Values],[Y Values],[Plot Order])
The Series Name can be blank, a text string in double quotation marks, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The X Values can be blank, a literal array of numeric values or text labels enclosed in curly braces, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The Y Values can be a literal array of numeric values enclosed in curly braces, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The Plot Order can only be a whole number between 1 and the number of series in the chart.
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. However, these items are not constrained to come from the same sheet. Each can come from a different sheet, even a different workbook.
Selecting Allowed Data from Different Sheets
Source Data Dialog
Chart Series Formula
Summarizing Data from Different Sheets
Suppose you still need to plot data that has Y Values on three different sheets, for example monthly statistics for three different years, where each year is on a different sheet. You have two options:
The first of these is straightforward. Add a series as described above, then select its data range from the appropriate sheet. Or copy the data, select the chart, and use Paste Special from the Edit menu to add the copied data as a new series. Format the different series to have the same formats (same markers, colors, etc.) so they appear to be from the same data source.
To generate a summary sheet, copy each of the ranges from the individual sources, and use Paste Special from the Edit menu, with the Paste Links option, to add the data to a summary sheet. Using the Paste Links option links the pasted data, so it updates with the original cells.
If the data is arranged the same on all the different sheets, you can use formulas on the summary sheet. For example, to summarize five worksheets, put the worksheet names in cells A2:A6, and put the cell addresses for the X and Y values into cells B1 and C1. In cell B2, put the following formula, and fill it right to C2 and down to B6:C6.
Note: if the sheet names have spaces, you need to enclose them in single quotes, so the formula should be
The table will look like this when filled in:
Construct your chart using B2:B6 of the summary sheet for your X values and C2:C6 for your Y values.
Select Data from a Different Sheet using a Forms Toolbar Control
ChartAgainstStandard.zip contains a zipped Excel workbook showing how to use a listbox or combobox to select which sheet contains data for a charted series. The listbox or combobox are used to select the sheet, and the INDIRECT worksheet function uses it to build cell references to the selected sheet.
Peltier Technical Services, Inc., Copyright © 2017. All rights reserved.