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