Multiple Workbook Consolidation Challenge

[cbzdmh]

Hi All,

I'm looking for some ideas/conceptual help.

We have a resource tracker following capturing the usual, sickness, holiday, absent kind of details but we're tracking for 150+ staff based in 10 teams in seperate locations.

These teams have designated team leaders who are responsable for tracking their resource, currently we take a master workbook, create copies and circulate to the teams to complete/update, we end up with 15 workbooks we have to merge into one each week, this is a massive overhead and waste of time as you can imagine.

We can't use a shared workbook/databse or web based form due to network restrictions, so Excel is the only option I can think of.

I was thinking of writing Macros to do the following:

1. From master workbook, create workbook for each team to complete with same format and defined ranges.
Once workbooks completed and returned, they go into 1 folder.
2. Macro to run through each workbook to check for changes to the ammended ('child') and copy these changes to the master ('parent').

I think i've got the basic code skills to do this but I'm not sure where to start checking for differences in the 'child' workbooks and then putting those into the master. Also once I've added the changes from the first 'child' workbook how to stop the second 'child' workbook simply overriding these changes.

Any help you can give me would be greatly appreciated.


Regards

D

 

[crimson_b1ade]

Problem solved!

 

[cbzdmh]

Not quite what i was looking for (we can't guarantee our teams will have web access) , but thanks for the link.

 

[crimson_b1ade]

..then i would say you need to post a sample of how your workbook is setup. otherwise no one is going to be able to provide you code.

 

[crimson_b1ade]

you could also probably use an Excel query to pull in the data into a Master excel sheet from the various xl files each week by overwriting the source excel files with the new source excel files (using no code at all)

 

[cbzdmh]

I understand it's difficult without code, maybe I should attempt and repost when I come to a roadblock.

I did think about linking the cells directly to other workbooks but then it becomes a task to ensure you distribute the correct workbooks to the correct teams.

I've had the idea that when I create the workbook creating 3 sheets as follows:

Sheet 1. - Copy of the resource tracker to be ammended by Team leader
Sheet 2. - Copy of resource tracker data
Sheet 3. - Full of formulas ('=if(sheet1!A1<>sheet2!A1,Sheet1!A1,False') to compare sheet1 values to sheet2 values - higlighting where changes have been made

The import data macro will then run through Sheet3 looking for evidence of values being changed, when the code finds that a cell has been changed, it then puts this value into the Master workbook.

 

[crimson_b1ade]

why are you hung up on items that need to change or that have changed. if each individual group maintained their own file (cumulative) then each time you would re-create the Master file based on the amended source files. I wouldn't even attempt to try and figure out what changed in the source file to try and change that in the master file as that would be too tedious and very error prone.