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

[sp_key]

Hi,

As part of my work, I receive reports on a daily basis with various data.
All daily Excel documents are named in the same way (today's date) and stored in the same place.

I now want to build a Master document that automatically reads the data from the daily reports once opened.
My approach was to have the actual date on column A, convert it to text on column B, specify location in column C using the below line:
='\\cpmpanyname\private$\location-x\...\my-reports\[B1]Financials'!$H$4

This approach isn't really working as it launches a Window and asks me to confirm the location and file.

Any help would be highly appreciated!

Cheers
Spyros

[Olly]

Are you really still using Excel 2003?

[sp_key]

Hi Olly,

I'm using Excel 2016.

Cheers
Spyros

[Olly]

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

[sp_key]

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

[Olly]

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.

[sp_key]

Hi Olly,

I have uploaded three screenshots to illustrate what I'm trying to do.
The first screenshot is called Daily Reports and it's a screenshot of the folder that contains all my daily reports and the filename structure. Every time I receive one, I put it there.
The second screenshot is called Daily Report and it's a small sample of what it contains - one single number for sales
The third screenshot is called Master and it illustrates what I'm trying to do which is to grab that single number from the Daily report. This is the document I'll use to do my analysis.

As you will notice, my first approach was to just copy the location on column C (in Master) hoping it will work.
My second approach was to have the date on Column A, convert it to Text on Column B and copy the location on Column C hoping I could aggregate this with text on Column B.

I think I remember being able to do this in the past way before Get & Transform was a feature.

Any thoughts?

Many thanks in advance

[Olly]

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:

Please Login or Register  to view this content.