Linking multlple spreadsheets to a master spreadsheet

[barryh1606]

Afternoon,

I am looking to link various cells from multiple spreadsheets to a master spreadsheet automatically without overwriting the previous data.

The purpose of this spreadsheet is to create a master log of work done for all sites, the individual spreadsheets are basically a time sheet for each site and would like this information to copy over to the master.

**Column A=Day B=Date C=Site Name D=Description of work E=Time On F=Time Off G=Total Time Taken**

I have looked at various ways of doing it, but cant work out how to update the master spreadsheet and not overwrite previous data.

Also each individual spreadsheet will have data in the same rows as the other spreadsheets and again this seems to overwrite the data rather than using the next available row.

Hope this makes sense, any more information required please let me know.

 

[sandy666]

download .zip file, unpack to C:\ drive then open master.xlsm
I changed it because I don't like bells&whistles, sorry

to see how it works PowerQuery (Get&Transform) is required (Excel 2010, 2013 add-in, 2016 and above - built-in)

zip file

edit:
btw. workbook is a single file with worksheet(s) so you posted workbooks

 

[sandy666]

Here is my way because in your source files is too much typing

download .zip file, unpack to C:\ drive then open master.xlsm

if you want another location, set it but you will need to change path from master.xlsm to test files.

after open master.xlsm move test4.xlsm to the test folder then refresh (Ctrl+Alt+F5 or right click on table and select Refresh) Query Table
do the same with test5.xlsm
next you can move out eg. test3.xlsm from test folder and refresh QueryTable

Days and Duration (column Total) will be added automatically , no need to type it everytime in source files

zip file 2

 

[barryh1606]

I asked how do you want to activate the script.
Preferably with a macro or VBA Script

I asked if you double click on A1 to copy Row(1) to master sheet would that work. And you did not answer that question.
Yes in theory it would work, not sure how that works if there is data in A1 on different worksheets.

Are you saying there will only be data on Row(1) of these other sheets.
No, there will be data in more than one row

So when would we want the Master sheet updated
Ideally i would want the master to update after I upload any new data to the worksheets, but if I have to open the master worksheet after i have updated my worksheets then so be it.


Thanks
Barry

 

[My Aswer Is This]

I'm just not understand what your doing here:
You said:

Ideally i would want the master to update after I upload any new data to the worksheets,

So your not manually entering data to these sheets your uploading data to the sheets from some place.

​

 

[barryh1606]

Hi,

Apologies i haven't replied sooner, I have been away working.

Sandy666 - Your Zip file 2 worked perfect for me.

Thanks guys for your time and support with this, it was much appreciated.

Have a nice weekend.

Barry

 

[sandy666]

You are welcome
Have a nice day