use workbook.open/close then from the slave workbooks to dump data to the master workbook, although have a look at how to open password protected workbooks

quote:

---

Originally posted by Dom
Are you trying to link to another worksheet or a complete new workbook(ie: new excel file)?

Like Ian said, you thought about creating an Access database (store it on a shared drive if need be) and then link to the db? The "master" spreadsheet could then grab the data from the access db and sort it accordingly....
Look up Excel VBA Access Database, plenty of info about.

You can also use Workbook.open to open another workbook (say the master) and you can then you can dump data to that - ie:

code:


Dim wBook As Workbook

Set wBook = Workbooks.Open("C:\MasterRecords.xls") ' Path to external file, can link to external storage using \\StorageBox\MasterRecords.xls



'****Do Stuff****



wBook.close

Set wBook = Nothing

The only issue i think you could have is if the MasterRecord workbook is password protected, as you'd need to put the password in the vba code and i cannot remember if you can stop people looking at your VB code.

Google around using the search term - Excel VBA Open Workbook <- plenty of stuff about :thumbs:

---

Are you trying to link to another worksheet or a complete new workbook(ie: new excel file)?

Like Ian said, you thought about creating an Access database (store it on a shared drive if need be) and then link to the db? The "master" spreadsheet could then grab the data from the access db and sort it accordingly....
Look up Excel VBA Access Database, plenty of info about.

You can also use Workbook.open to open another workbook (say the master) and you can then you can dump data to that - ie:

code:


Dim wBook As Workbook

Set wBook = Workbooks.Open("C:\MasterRecords.xls") ' Path to external file, can link to external storage using \\StorageBox\MasterRecords.xls



'****Do Stuff****



wBook.close

Set wBook = Nothing

All excellent ideas, Doug that wouldbe ideal but i simply don't have the skills.

Dom, a VB button is the only bit im struggleing with, ive got both speadsheets sorted and all the formula working.Staff enter how many of each 'task' they have done each day, it gives a productivity % at the bottom(which is hidden as they are not supposed to see this). All i need is a button that places this in the 'overall' spreadsheet which is password protected and only me and my manager see.

Another department have adoped the idea of a spreadhseet each for staff that feeds into an overall sheet, the individual sheets are PW protected but my manager does not like this because when it goes wrong and staff are off nobody can get into their sheets etc.

Can you protect each sheet individually?

If not you could try separate files and an Access ODBC link to each of the files to run the queries.

Could use VB script for the button (can place it directly on the sheet) and processing of the info, isn't the easiest thing in the world but you could grasp VB within a day or so.

Another solution is to create an excel workbook with multiple sheets that are each password protected - so every person has their own sheet and password (so no one can change each others). Get this stored on a central server and you can then keep an overall excel spreadsheet and add in the figures accordingly.

Would it not be better to knock up a little program that they can use to just enter the productivity? Makes it less open to abuse

Anybody? Sounds confusing but its really not,lol. :(

Ive basically been asked by my manager to create a spreadsheet that records the productivity of the 10 team members each day. It must be automated and they must complete it themselfs.

Thefrefore i have created one speadsheet that the staff completed themselfs each day, they select their name and the date from the top and then enter in how much work they have done.

Ive then created a seperate overall spreadsheet that caputres this data and gives and average for that person and the team each month.

The bit where im struggleing is creating a button on the spreadsheet that the staff complete so that once they have updated with the amount of work they have done, they click submit and it feeds their overall percentage for that day through to the overall sheet, as well as clearing the form for the next person....

I can email both the spreadsheets if anybody is willing to have a look at it for me? :)