Creating chart from multiple worksheets

I need to create a chart from data contained in multiple worksheets (all in the same workbook). I am using Excel 2003. I need to chart a specific cell within each worksheet. It's the same cell in each worksheet, say B7. I have about 100 worksheets. So I need to be able to select all 100 worksheets and tell Excel to chart the progress of the B7 cell in each worksheet. Each worksheet contains data for one day and is named after the date/time of creation. So really the chart is to show the value of the B7 cell over 100 days.

Does that make sense? Can anyone help me to create this chart?

TIA,
Martin

Building on Bryan's excellent response ...

Assume that the chart is on Sheet1, the first data point is on Sheet2, and that each new Sheet added to the workbook will be named in sequence ... Sheet3, Sheet4 ... Sheetn.

On Sheet 1, starting in Row 2 (any column you like, but I used column B for this example), you put this formula:
=IF(ISERROR(INDIRECT("Sheet"&ROW(A2)&"!B7")),"",INDIRECT("Sheet"&ROW(A2)&"!B7"))
Drag that formula down a few rows (eventually, you will want to drag it down a few thousand rows).

Then, add a Name to the workbook, using Insert >> Name >> Define. I used the Name "Series", and in the refers to box put this formula:
=OFFSET(Master!$B$1,1,0,SUMPRODUCT(--(Master!$B$1:$B$65000<>"")),1)

I assume the chart is already set up. Select the series. In the Excel formula bar you will see a formula that looks something like this:
=SERIES(,,Sheet1!$B$2:$B$4,1)

Select this part of the formula: $B$2:$B$4
And type in "Series" (without the quotes).

If this looks like what you want, go ahead and drag down the formula
=IF(ISERROR(INDIRECT("Sheet"&ROW(A2)&"!B7")),"",INDIRECT("Sheet"&ROW(A2)&"!B7"))
for a few thousand rows ... or as many as you think you will ever need.

What the formula does is automatically populate Sheet1 as new sheets are added.

What the Name does is redefine the range that is charted so that it only includes non-blank results.

Originally Posted by Bryan Hessey

Hi,

If you copy/paste each B7 to the chart they will be as separate series.

If you =Sheet1!B$7 etc into the next 100 rows then you can chart as a single series
or
=indirect(D1&"!B7") where column D has a list of sheets etc

either will update as the other sheets updae.

hth
---

Thanks Bryan. Is there anyway to tell Excel to use all my worksheets? Rather than having to type in each one into the formula. They are not named contiguously. They are named with the date and time stamp of creation. Eg '21.01.2007 19-10-59'.