How do I read info from different worksheets into a summary sheet?

[Guest]

We do hourly analyses of water covering five items, like pH, iron, sulphate
temperature, etc. We have 30 or 31 sheets (1 sheet for every day of the
month) with 24 hour rows with averages of each analysis at the end, i.e. the
average of the day's analysis per column. I want to have a separate sheet
with every day's average for each item tested for. How do I enter the items
average from each sheet without doing it manually by entering = and then
going to the sheet and the reading and clicking it.

 

[Max]

One way ..

Assuming the 31 sheets are named sequentially as:
Sheet1, Sheet2, ... Sheet31

and the average figures in each sheet are in row 26,
viz. in: A26, B26, C26 ... Z26 etc

In a sheet: Summary (say)
----------
List the sheetnames down in say, A2:A32,
i.e.: Sheet1, Sheet2, ... Sheet31

Put in B2:
=OFFSET(INDIRECT("'"&$A2&"'!A1"),25,
COLUMNS($A$1:A1)-1)

Copy B2 across as many cols as there are figures to be pulled over from each
sheet, then fill down to row 32

The above will extract the results that you're after

 

[Guest]

Hi Max,

What if the sheets aren't named sequentially!

When I go to the properties of the workbook I can see all the worksheet
names listed under the contents tab. BUT I cant copy that as text into the
spreadsheet.

Do you know how I can get the list of worksheet names copied into a column ? !

Cheers

 

[Max]

.. Do you know how I can get the list of
worksheet names copied into a column ? !

Try the sub below:

Steps
--------
Press Alt+F11 to go to VBE
Click Insert > Module
Copy > paste everything within the dotted lines below
into the whitespace on the right

-------begin vba-----
Sub SheetNames()
'Peo Sjoblom in .worksheet.functions Jul '02
Dim wkSht As Worksheet
Range("A1").Select
For Each wkSht In Worksheets
Selection = wkSht.Name
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next wkSht
End Sub
-------endvba------

Press Alt+Q to get back to Excel

In a *new* sheet, press Alt+F8
Select "SheetNames" > Run

The sheetnames will be listed in A1 down, in this sequence:

1st sheet (leftmost) will be listed in A1,
2nd sheet in A2, and so on

Hidden sheets will also be listed
and will appear after the last (rightmost) sheet