how to chart same cell in multiple sheets

  • Thread starter Thread starter bn2wild
  • Start date Start date
B

bn2wild

I have an excel template with 3 worksheets that I created
for others to input information into certain cells on each
sheet on a daily basis, and resulting save with similar
file names. I want to compare the information between
workbooks (plotting) to see if the trend results are going
up or down.

Is there a way to plot a chart, based upon the information
in one specific cell (or same specific group of cells)
within multiple sheets (excel 2000 or 97)?

I can present the information in various ways.
1) Same cell or group of cells within same named
worksheet, over many workbooks (files).

or I can combine the workbooks into one with each workbook
becoming a worksheet tab, then:

2) Same cell or group of cells within different named
worksheets, in one workbook (file).

Any ideas?
Thanks,
bn2wild
 
The data has to be in a single sheet. Suppose you want to track the
values of cells A1 and B1 in sheets 1 through 3. You could set up
formulas in a range like this:

Leave cell A1 of a summary sheet blank, put A1 into cell B1 and B1 into
C1. List the sheets in A2 to A4:

A1 B1
Sheet1
Sheet2
Sheet3

select the range B2:C4, with cell B2 as the active cell. Enter this
formula into B2:

=INDIRECT($A2&"!"&B$1)

and hold Ctrl while pressing Enter. This populates the range with values
from the other sheets.

Note: if the sheet names have spaces, you need to enclose them in single
quotes, so the formula should be

=INDIRECT("'"&$A2&"'!"&B$1)

Also, a blank in the source sheet will become a zero in the summary sheet.

- Jon
 
Back
Top