Linking multlple spreadsheets to a master spreadsheet

barryh1606

New Member
Joined
Nov 14, 2017
Messages
11
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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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>
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top