Consolidate Multiple Workbooks Into One And Push Back Down Updated Data

[myexcellent123]

Hello! First of all, thank you in advance to anyone reading this!

So, here is my situation, I have a set list of Unique IDS and standard row of same, header columns across multiple workbooks. Alll of these are saved on a central Sharepoint webpage.

These wkbs are split between 3 depts. I have a total of about 275 columns and 1300 Unique IDs.

The first dept has split the Unique Ids and updates across all columns. Easy to merge and i have the macro that accomplishes this. Basically it opens all files and copies and adds one wkb beneath each other into one single sheet.

The second dept makes it complicated, as it has split COLUMNS (vs. Unique IDS) and multiple people are updating the same Unique ID but for different columns.

The third dept (single person) works on the comprehensive list of all Unique IDs but has his own section of columns.

So, the challenge is:

1.To merge all files across all dept into ONE MASTER WKB. I could do this in Access (linked via Unique ID) but it exceeds the 255 column limit, as this would go out to 275 columns.

.....then with all *updated* data off these files

2.To push all *updated* data back out to the individual files.

The attached sample Wkb might make this easier to understand. Please assume that each tab in this file is a separate workbook.

I truly appreciate any help on this!

i also posted this on (Consolidate multiple Wkbs into ONE-update-then push back down updated data | Chandoo.org Excel Forums - Become Awesome in Excel)