Thread: vlookup on multiple workbooks into 1 main sheet

I know it sounds cumbersome, but this sounds like the kind of thing you would only have to type ONE formula accurately for each agent to get the data for one particular cell to appear in your workbook. As long as you keep the cell reference relative, you can then copy that cell downward to get the rest of the stats for that agent into your workbook.

That would enable you to collect the values into your master workbook and then do the percentage calculations locally.

Does that make sense? It's a little work, but it's only a one-time setup, then the formulas are there and working forever, yes?

For example:

='C:\My Documents\Jerry\[Test.xls]Stats'!D3

Two things...

1) If you want to do an "on the fly" report, we can create a macro that
....opens every file in a specific folder
....copies data from specific cells into your "report" row by row
....runs "on demand" so you can collect this data when you wish
....or, runs "when opened" so anytime you open the report it collects the data for you

2) I can help you tweak your existing code to properly insert a series of formulae row by row for each workbook in a folder


I agree that one workbook with that many remote links may be more prone to error, but that's from the hip. It may be fine.

If it were me, I'd be creating a "flat" report that I call "on demand". If you agree, give me:

1) The path with the files
2) All files in that folder? Or just specific files (give me the filtering instructions in the name)
3) A list of the cells you want to grab (and what they are so we know what to title those columns)
4) The report itself...it should be completely new each time or append to the bottom?

Don't skip any of those questions.

Here's a variation on my standard Consolidation macro. I shows you how to setup the first 4 columns in your Summary report. Be sure to edit the line underlined to the correct sheet name for your consolidation report.

Code:

Option Explicit

Sub Consolidate()
'Author:     Jerry Beaucaire'
'Date:       4/16/2010     (2007 compatible)
'Summary:    Open all Excel files in a specific folder and imports
'            key date into a Summary sheet, one row of data per workbook
Dim fName As String, fPath As String, OldDir As String
Dim NR As Long
Dim wbData As Workbook, wbkNew As Workbook

'Setup
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
   
    Set wbkNew = ThisWorkbook
    wbkNew.Activate
    Sheets("Summary").Activate   'sheet report is built into, edit to correct sheet name
   
    If MsgBox("Import new data to this report?", vbYesNo) = vbNo Then Exit Sub
   
    Cells.Clear
    Range("A1:D1").Value = [{"Workbook","Cost","Data1","Data2","Data3"}]
    NR = 2

'Path and filename (edit this section to suit)
    fPath = "D:\Archem\Adam mixes\"     'remember final \ in this string
    OldDir = CurDir                     'memorizes the users current working path
    ChDir fPath                         'activate the filepath with files to import
    fName = Dir("*-*.xls")              'start a listing of desired files, edit the filter as desired

'Import a sheet from found file
    Do While Len(fName) > 0
        If fName <> wbkNew.Name Then    'make sure this file isn't accidentally reopened
        'Open file
            Set wbData = Workbooks.Open(fName)

        'This is the section to customize, replace with your own action code as needed
            With wbkNew.Sheets("Summary")
                .Range("A" & NR) = wbkNew.Name
                .Range("B" & NR) = Sheets("Cost Summary").Range("A5")
                .Range("C" & NR) = Sheets("Cost Summary").Range("C7")
                .Range("D" & NR) = Sheets("Cost Summary").Range("E15")
            End With
            
        'close file
            wbData.Close False
        'Next row
            NR = NR + 1
        'ready next filename
            fName = Dir
        End If
    Loop

ErrorExit:    'Cleanup
    ActiveSheet.Columns.AutoFit
    Application.DisplayAlerts = True         'turn system alerts back on
    Application.EnableEvents = True          'turn other macros back on
    Application.ScreenUpdating = True        'refreshes the screen
    ChDir OldDir                             'restores users original working path
End Sub

(Original macro)

You can add a hyperlink like so:

Code:

            With wbkNew.Sheets("Summary")
                .Range("A" & NR) = wbkNew.Name
                .Hyperlinks.Add Anchor:=.Range("A" & NR), Address:=fPath & fName
                .Range("B" & NR) = Sheets("Cost Summary").Range("A5")
                .Range("C" & NR) = Sheets("Cost Summary").Range("C7")
                .Range("D" & NR) = Sheets("Cost Summary").Range("E15")
            End With

This technique will not delve into subfolders.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Hijacking someone else's thread wasn't cool, and it against the forum rules. Oh well, the OP appears to have vanished anyway.

Are you asking something else?