Synchronising Excel Documents Across SharePoint

Hi guys!

I'm new to this forum, so apologies if this has come up somewhere else. Although I've had a thorough look around and I haven't been able to find the answer, so I'm hoping you may be able to help me. By the way, I'm fairly new to this level of Excel coding, so you may have to bear with me.

I was wondering if any of you knew how to synchronise and automatically update certain fields on a large summary Excel document from the inputted data placed in other smaller Excel files, all across SharePoint, without having to download the documents to the local drive and uploading them back up. Let me explain....basically we have a number of project-related files on SharePoint. The "Mac Daddy all encompassing Excel project document" compiles all the information from various different individual, smaller, process-specific Excel documents. Currently this is all done manually, which is obviously very time consuming. As far as I'm aware, you can't quite do a straight forward updating formula in SharePoint as you would do if all this was happening in my local drive.

So any ideas on how I can do this? Do I need to combine Web Query Look Ups into the macro that would make all this work? (Btw, I have no idea about how to use Web Queries, so if they are necessary, you may have to explain those a little bit). Ideally this updating would be done automatically upon saving/closing the updated smaller process-specific documents, and this would update the fields in the master file. Or even if its done by pressing an Update button that has the necessary macros/VBA coding imbedded in it. If you could explain it all in crystal clear terms and write the coding exactly as I would need to enter it I would be eternally grateful. Thank you very much in advance

I look forward to hearing from you soon guys!

Cheers

One strategy that you might consider is to build some functionality into your master workbook which pulls the relevant data from the source workbooks either when you open it or hit an update button. Such code would open the source workbooks sequentially as read only and then close them once the data had been extracted and finish with a save of the master workbook.

The exact code would obviously depend upon the format of the individual workbooks involved.