How to consolidate several excel spreadsheets to observe trends?

[PrayForDeath]

I have this spreadsheet template that I fill up with numbers for work daily. So every day I end up with a new spreadsheet, and I have hundreds of them now.

I’m looking for a way to consolidate or merge these spreadsheets in order to observe trends. They contain quality control numbers, and I’m hoping to build some useful charts out of them that I can study.

Is this possible? I have somewhat limited Excel knowledge, and no Access knowledge.

Any help would be appreciated!

 

[Edgy]

Don't quite understand exactly what "observe trends" is that needs to be achieved.

An example or more specific requirement description would be helpful.

One can use excel to merge or resource data from multiple sheets and/or files fairly easy enough. Not much you can't do with excel...

 

[PrayForDeath]

I'll try to explain this as best I can. I have this form that gets filled 3 times a day (have about 1000 of them now.) They all have identical formatting, and at the bottom of the form there's a row that calculates averages for each column.

What I want is to consolidate this last row from all the spreadsheets and observe how the numbers are changing over time.

If you know an Excel or Access macro that would accomplish this I would be very grateful to learn it.

 

[postmark]

Found this with a quick google search. Should get you going as a macro. Hopefully all these workbooks are in the same directory.

'=========================================================
'- CONSOLIDATE DATA SHEETS
'- (ALL WORKBOOKS IN FOLDER.ALL SHEETS)
'=========================================================
'- Generic code for transferring data from
'- all worksheets from all workbooks contained in a folder
'- to a single sheet.
'- Change "Sub Transfer_data()" etc. as required.
'----------------------------------------------------------
'- Workbooks must be the only ones in the folder.
'----------------------------------------------------------
'- worksheets must be contain tables which are
'- identical to the master, headings in row 1.
'- *master sheet is remade each time*
'- run this code from the master sheet (with headings)
'- by Brian Baulsom (BrianB) January 1st.2004
'----------------------------------------------------------
Dim ToBook As String
Dim ToSheet As Worksheet
Dim NumColumns As Integer
Dim ToRow As Long
Dim FromBook As String
Dim FromSheet As Worksheet
Dim FromRow As Long
Dim LastRow As Long
'-
'=========================================================
'- MAIN ROUTINE
'=========================================================
Sub FILES_FROM_FOLDER()
    Application.Calculation = xlCalculationManual
    ChDrive ActiveWorkbook.Path
    ChDir ActiveWorkbook.Path
    ToBook = ActiveWorkbook.Name
    '---------------------------
    '- MASTER SHEET
    '---------------------------
    Set ToSheet = ActiveSheet
    NumColumns = ToSheet.Range("A1").End(xlToRight).Column
    ToRow = ToSheet.Range("A65536").End(xlUp).Row
    '- clear master
    If ToRow <> 1 Then
        ToSheet.Range(ToSheet.Cells(2, 1), _
            ToSheet.Cells(ToRow, NumColumns)).ClearContents
    End If
    ToRow = 2
    '------------------------------------------
    '- main loop to open each file in folder
    '------------------------------------------
    FromBook = Dir("*.xls")
    While FromBook <> ""
        If FromBook <> ToBook Then
            Application.StatusBar = FromBook
            Transfer_data   ' subroutine below
        End If
        FromBook = Dir
    Wend
    '-- close
    MsgBox ("Done.")
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
End Sub
'
'==============================================================
'- CHANGE THIS CODE TO DO WHAT YOU WANT TO THE OPENED WORKBOOK
'- HERE IT COPIES DATA FROM ALL SHEETS TO THE MASTER SHEET
'==============================================================
Private Sub Transfer_data()
    Workbooks.Open FileName:=FromBook
    For Each FromSheet In Workbooks(FromBook).Worksheets
        LastRow = FromSheet.Range("A65536").End(xlUp).Row
        '-----------------------------------------------------
        '- copy/paste to master sheet
        FromSheet.Range(FromSheet.Cells(2, 1), _
            FromSheet.Cells(LastRow, NumColumns)).Copy _
            Destination:=ToSheet.Range("A" & ToRow)
        '-----------------------------------------------------
        '- set next ToRow
        ToRow = ToSheet.Range("A65536").End(xlUp).Row + 1
    Next
    Workbooks(FromBook).Close savechanges:=False
End Sub
'==== EOP ====================================================

This link would also be helpful.

http://www.rondebruin.nl/copy3.htm

 

[PrayForDeath]

I will give that a try. Thank you