Extracting Data from Multiple Excel Files into One Excel File

Hello there,

I am trying to extract some specific data from multiple excel files. All of the excel files are standard. But in some excel files, the place of the requested cells are changing.

What I would like to extract from the excel files:

1 - Name of the Excel File
2 - Date
3 - Invoice Number
4 - Total Amount of the Invoice


Because the location of the requested cells are changing, i think I need to find the “Date” or “Total Amount” words and export them to another data table.

I have attached the project folder here.
MultipleFiles.zip (23.2 KB)
Here is the sample for one of the excel files.
001 UZ YD EVRAKLAR.xlsx (21.8 KB)

Any advice how to do this correctly?


I forgot to write.
In every excel file there are 2 or some time 3 sheets. And The data I want is available in the first sheet.