Geniusduffer
New Member
- Joined
- Mar 15, 2011
- Messages
- 2
I work in a company where team leaders plan the quarterly site visits. There are mainly three types of visits as shown in the workbook. Field staff get to see the plan for each month and update their work on it as and when they can during a month. All the staff have their own machines/laptops with unique login IDs on our intranet and each machine has a unique asset no.
I dont know how to upload the workbook, please ask for it and I can email it you or advise how to upload it here.
Team leaders plan the quarterly visits as in sheet ‘Planning’ under three main types. I want this sheet to generate 12 sheets (or 3 for each quarter), one for each month with details of only planned sites. I have shown only 25 sites here whereas the actual numbers are much more, so the need for omitting non-planned sites is quite important. Another requirement is that when a team leader plans more than 1 inspection for a site, it should result in two rows in a monthly sheet for field workers to update their work. One site with multiple inspections is often visited by different people on different dates, so need for more rows.
Now the field staff update these monthly sheets (like the ones named from Apr-Mar). I have included a column for names for staff to input their names against each completed visit. I think the login IDs or machine no could help in omitting this column and excel should automatically include the person’s name based on their ID/Machine no. But, if this is not possible-its not a problem.
The next step is that when field staff update the monthly sheets, team leaders and HR need the data of visits to calculate work done. I want the monthly sheets to update already existing sheet – ‘Work record’ based on the details updated by the field staff for only completed visits according to dates and preferably grouped by months.
I have tried my best excel skills and put in few formulae till I gave up to find a decent solution. If you think the layout of the sheets needs a bit of modification, please do so, as long as it contains the basic info as I have included.
Finally, my thanks to all those who will help me out of this problem.
Ciao <!-- / message --><!-- attachments -->
I dont know how to upload the workbook, please ask for it and I can email it you or advise how to upload it here.
Team leaders plan the quarterly visits as in sheet ‘Planning’ under three main types. I want this sheet to generate 12 sheets (or 3 for each quarter), one for each month with details of only planned sites. I have shown only 25 sites here whereas the actual numbers are much more, so the need for omitting non-planned sites is quite important. Another requirement is that when a team leader plans more than 1 inspection for a site, it should result in two rows in a monthly sheet for field workers to update their work. One site with multiple inspections is often visited by different people on different dates, so need for more rows.
Now the field staff update these monthly sheets (like the ones named from Apr-Mar). I have included a column for names for staff to input their names against each completed visit. I think the login IDs or machine no could help in omitting this column and excel should automatically include the person’s name based on their ID/Machine no. But, if this is not possible-its not a problem.
The next step is that when field staff update the monthly sheets, team leaders and HR need the data of visits to calculate work done. I want the monthly sheets to update already existing sheet – ‘Work record’ based on the details updated by the field staff for only completed visits according to dates and preferably grouped by months.
I have tried my best excel skills and put in few formulae till I gave up to find a decent solution. If you think the layout of the sheets needs a bit of modification, please do so, as long as it contains the basic info as I have included.
Finally, my thanks to all those who will help me out of this problem.
Ciao <!-- / message --><!-- attachments -->
