How do I get data from daily reports into a master document based on filename and location

Are you really still using Excel 2003?

Then you can use Get & Transform Data to combine data from all workbooks in a folder.

Isn't there an easier way?
The Get and Trasform solution is quite involved for what I'm trying to do.

Ideally, all I want to do is open my Master Document.
This will point to other documents at specific cells . It should then take a few seconds to load and grab the data and I can then analyse on a separate sheet.

From what I understand, Get & Transform requires me to manually get each separate document and preprocess it.

currently, my daily reports are not that greatly formated (with lots of merged cells and irregular table formats). The people who send these reports to me are not that familiar with Excel.
However, the data I need is always in the same cell so I should be able to map to it directly from a Master document?

I do remember doing this in the past way before Get & Transform was available

Am I missing something?

Many thanks in advance

Give me an example of your input / output structures.


Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Screenshots are such little help... The second screenshot tells me exactly nothing.

The third: again, screenshots are so limited. The path you're typing in your attempted formula doesn't match the path in screenshot 1. I can't tell why you need to have a [Date to txt] column.

To refer to a fixed cell in a variety of workbooks, using a workbook name (partially) stored in another cell, you could use the INDIRECT function; but that will not work with closed workbooks, so doesn't meet your requirement.

You could write a quick bit of VBA, to loop through the output cells in Column C, and write in the formula which will refer to the closed workbooks:

Sub foo()
    Dim l As Long
    Dim s As String
    With Sheet1
        For l = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            s = "='" & Environ("UserProfile") & _
                "\Documents\My Data\Daily Reports\[" & _
                Format(.Cells(l, 1).Value, "dd-mm-yy") & _
                ".xlsx]Financials'!$D$5"
            .Cells(l, 3).Formula = s
        Next l
    End With
End Sub