Search a text into a folder containing worksheets with different names and copy values

[Alincristi]

Hello all,

I am searching for a solution in excel to search for some data into a specific folder which contains more worksheets with different name and if the text is fount to copy the data into another excel file.

Please see below some images to better understand what i want to achieve.

I have the folder below

Every Testing.xlsx file contains the information below:

In my current worksheet i have the table below.

In this worksheet i want to write in cell B3 for example a serial number ('160057819") and then to automatically populate the Part 2 cells and Data and Data 2 with the information found.

Is there anyone can help me solving this problem :)

Much appreciated!

[#2]

@Alincristi 

I see two ways to do it.

1. INDIRECT - the drawback is INDIRECT doesn't like closed workbooks. The files would need to be open to pull the data.

2. VBA - the best solution. Write a loop to open the workbooks, fetch the data, and return the values to the desired sheet.

[#3]

@Patrick2788 Thank you for your quick reply.

I tried to look at the VBA option, but i am at the beginning with the VBA and didn't figure it out how to do it. 

[#4]

@Alincristi 

I'd do it in two parts.  The first part presents you with an open dialog to select the files. The next part writes each open files' data to your sheet.  Just need to know the cell addresses in each workbook and where to write the data in the destination file.

[#5]

@Alincristi 

Since all Testing files have the same structure and are in the same folder, I'd combine all of them, with some transformation, by Power Query into one table in main file and lookup this table for data required.