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]

try PowerQuery (Get&Transform) with Append feature

 

[barryh1606]

Hi Sandy666, thanks for replying.

Can you maybe give me an example, please.

 

[sandy666]

post a link to shared example excel file with sample data

 

[My Aswer Is This]

Current Excel Terminology does not use:

spreadsheets

spreadsheets was a term used in the Mid 1960's to refer to what we now Refer to as a Workbook.


Today we use the terms:

Workbook and Worksheet.

So when say

spreadsheets

Do you mean Workbook or Worksheet.

You said:

I am looking to link various cells from multiple spreadsheets to a master spreadsheet​

​

​

_{^{<strike>
</strike>}}​

 

[barryh1606]

Hi,

Apologies, I meant worksheets..

 

[My Aswer Is This]

So if I understand correctly if a user modifies anything on any sheet in the Workbook you want this data copied to the Worksheet named "Master"

So if that is what you want we would need to know what would activate the script.

Now if John entered "Hello" on Row(3) of Sheet(3) I would not think you would want this entire row copied to the Master Sheet.

So when would we want the Master sheet updated?

We could have that row copied over when John Double clicks on that rows Column(A)

So if John wanted row(5) copied to master sheet He would Double click on Range("A5")

Would this work.
Or provide some other way.

 

[barryh1606]

Yes that would be correct.

All cells in each row need to be filled out with information anyway

A1 = Day
B1 = Date
C1 = Customer
D1 = Time In
E1 = Time Out
F1 = Total Time

Ideally the master worksheet would pull all new data from the linked worksheets it already knows about and I would be able to add to the script\formula any new worksheets that are created.

All the cells are the same on every worksheet including the master. Basically they are just a copy of each other.

My reason for doing this is because I need to log all calls\site visits that we do and then total the hours at the end of the week\month collectively. I was originally just going to use a sum formula from each worksheet from the total time field to get a total of hours for all sites, but would be prefer if i could have a master worksheet of all calls and times so that i can work out if the site is paying too much or too little for there contract by the amount of time spent on each task they require.


This is the master spreadsheet - https://www.dropbox.com/s/6l89ywr4f5mpcyt/Master Workload.xlsx?dl=0

Test 1 Spreadsheet - https://www.dropbox.com/s/pwg8n4ki75pgox6/Test 1.xlsx?dl=0

Test 2 Spreadsheet - https://www.dropbox.com/s/33cu8tpii3ry4t2/Test 2.xlsx?dl=0

Not sure if you use Dropbox links or not....

Thanks
Barry

 

[My Aswer Is This]

I never open files.

I asked how do you want to activate the 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.

Are you saying there will only be data on Row(1) of these other sheets.

Or will there be more data on more rows.

 

[My Aswer Is This]

I asked:

So when would we want the Master sheet updated?

You only said:

That would be correct.

I'm not sure if that is the answer to my question.
​