Merge Data with Query Option

[Trying to Excel]

I have mutiple workbooks in a folder. All workbook data is on 'Sheet1' and
have the same format.
Row 1 contains headers.
Column A2:A20 contains dates
Column B2:B20 contains reference #'s such as 1356, 1378, 1478 etc
Column C2:C20 contains data pertaining to the Reference #.

How can I set up a macro on a master workbook that will pull the data from
all workbooks in the folder based on the information entered on the Master
Workbook.
As an example, on Master Workbook, Sheet1, A:1 = 1356, then all instances of
1356 and related data from all workbooks in the folder would be displayed on
the Master Workbook?

Your expertise, as always is appreciated

 

[ShaneDevenshire]

Hi,

If all the workbooks are open you can use the INDIRECT function, but this
function does not work against closed files.

I suspect you are going to need to create a macro. The macro could build a
formula that references the cells or it could bring in the value without
links. It all depends on what you want.

If you want to create formulas in cell, then open one of the workbooks and
create the formula by pointing and clicking. Then close the file to which
the formula is linked. Select the cell with the formula and turn on the
macro recorder. Press F2, Enter and turn the recorder off. Go and look at
the vba code that was created. See if you can generalize it to your overall
needs. If not post the code to here.