A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
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? 
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
Anybody? Sounds confusing but its really not,lol. 
|
Doug
Member
Registered: 8th Oct 03
User status: Offline
|
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
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
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.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
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.
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
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.
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
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 
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
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
Its linking to a complete new workbook, so that i can password protect the seperate book as even password pretected individual sheets can be viewed.
We dont have access at work, although if we did, none of the numptys in our department would be able to find the password in the VB code.
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
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
|
