multiple sheet selection

[gypsyeyes]

Hello fellow Excel users!!!
I am looking for a formula or macro that will list the data from the
same cell reference on multiple spreadsheets in excel 97.
I work with a program that contains a section of data that I want. I
then export this data into excel. Each test I run will have its own
excel worksheet. There can be up to eight tests a day, 5 days a week. I
would like to have all the tests form a workbook for each month. I am
thinking of having the last page of the worksheet a summary page where
I can make a column of all the data in sheets 1 to x, cell g4, another
for sheets 1 to x cell g5 etc. I would like to make columns because I
want to chart the data. Is there an easy "list" function so I don't
need to separately type in =sheet1!g4
=sheet2!g4 etc etc.
Also keep in mind that I will not necessarily have the same number of
worksheets in the workbook each month and that I would like to always
have a running total so I can perform a trend analysis.
Then, if something is found that will do this, it there a way to
reference the worksheet as well?
For example, the summary page would say...

Description Value
Sheet 1 value in g4
Sheet 2 value in g4

The sheet description is only in the tab at the bottom of the
worksheet.

This has been giving me quite the headache for quite some time!!
Please help if you can

Thank you!!!

 

[Vasant Nanavati]

Hi:

A very complicated solution, but you only have to enter the formula once and
then copy it. This assumes that the sheets are numbered Sheet1, Sheet2, etc.
and that the last sheet is the summary sheet. It also assumes that the first
column of your table summarizes all the cells G4, the second column g5, etc.

In cell C3 on the summary sheet, enter the formula:

=IF(AND("Sheet"&ROW()-ROW($C$3)+1<>RIGHT(CELL("filename"),LEN(CELL("filename
"))-FIND("]",CELL("filename"))),NOT(ISERROR(INDIRECT("Sheet"&ROW()-ROW($C$3)
+1&"!G"&COLUMN()-COLUMN($C$3)+4)))),INDIRECT("Sheet"&ROW()-ROW($C$3)+1&"!G"&
COLUMN()-COLUMN($C$3)+4),"")

If you want to start in another cell, substitute the absolute address of
that cell for $C$3 in the formula.

Now copy down and across as far as you want.

I haven't tested this thoroughly but it should work as long as you don't
insert/delete rows and columns in the middle of the data.

Regards,

Vasant.