Formula for Master worksheet to pull data from multiple closed workbooks...

I have a large matrix-style spreadsheet with 1,500 unique customer numbers in Column A, and 16 or so various headers in Row 1 (names, products, dates, part numbers, etc). This Master worksheet is formatted as a table, and the data is blank.

I have 5 employees who were each assigned 300 customer numbers to work on. Each employee made a copy of the master worksheet template, and saved it as a new workbook in their name ("Smith Master Copy", "Jones Master Copy", "Davis Master Copy", etc). The workbooks and the Master are all located in the same folder on our network, and the employees will not be changing the format on their copies- only entering data & saving. No 2 employees should be entering data in the same row/customer number (unless they make a human error).

What is the easiest way to get the Master worksheet to dynamically reference the data my employees are entering? I see 3 possible caveats: 1) the workbook copies will often be closed, 2) the Master Worksheet needs to consolidate the data from all 5 copies, & 3) if an error is made (2 employees entered different data in the same row on their respective workbook copies) then the Master needs to tell me that somehow.

Any thoughts? My best attempt was 5 separate INDEX,MATCH,MATCH formulas connected by "&". This only seems to work when the workbook copies are open, even though I have the full C: drive file addresses in the Array portions. I have lost my sanity trying to figure this one out, but I can't imagine I'm the only person who ever needed this type of formula? Much thanks in advance!

Hi Batman,

Can you Upload an Example Master Worksheet.

Regards

JRidge

Hi Batman,

Put this in a Standard Modul in the Master Document,

Please Login or Register  to view this content.

Change the FilePath to the correct one etc..

Run as Macro from Master Document.

Regards

JRidge