Combining Data from multiple workbooks into one workbook

[gazza_za]

Hi All

I have an excel workbook that has about 20 worksheets. Each worksheet has cells that have a drop down box where either Yes or No can be entered.

At this moment the workbook has been given to 5 different people to work on, each of whom complete a few sheets.

I would like to know how to take these 5 copied worksheets and combine them into one "master" worksheet that has all the changes made by all the people in it.

Please can you provide some assistance in this.

Thanks

[Damon Ostrander]

Hi Gary,

There are a number of ways to do this. If you have a situation where everyone is on the same computer network and therefore could access the the same file from a common network location, I would recommend you simply make the file "shared" (Tools -> Protection -> Protect and Share workbook), meaning that multiple people can edit the file simultaneously. So long as they don't try to edit the same sheet simultaneously (which would cause one of them to be temporarily locked from accessing the sheet) this has the advantage that there is no need to merge a bunch of separate workbooks.

A second way is to put the workbooks into Track Changes mode (Tools -> Track changes). This causes Excel to keep track of who has changed what. It would then be possible to write a macro that would read all the workbooks, find all the cells that have been changed, and write these changes to the master workbook. And incidentally, change tracking can be used with shared workbooks so that you can know who made each change.

Finally, you could write a macro that would compare each cell in the master workbook with the corresponding cell in each editor's workbook and accumulate all the changes in a new master workbook.

I hope this helps.