Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I read info from different worksheets into a summary sheet?
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. |
#2
|
|||
|
|||
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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Waterh2o" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I read info from different worksheets into a summary sh
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" wrote: 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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Waterh2o" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I read info from different worksheets into a summary sh
"anamcara" wrote
.. 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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transfer Info From Worksheets | Excel Discussion (Misc queries) | |||
Is it possible to transfer info between worksheets | Excel Discussion (Misc queries) | |||
Transferring info between worksheets | Excel Worksheet Functions | |||
Can a formula operate between worksheets, ie collating info from . | Excel Worksheet Functions | |||
Adding rows of different info from separate worksheets into summar | New Users to Excel |