Sir Babydum GBE
05-10-2006, 04:56 AM
I have a tricky dilemma, which I just can?t solve ? I?m hoping that your expertise will come to my rescue!
On the attached example workbook, I am trying to track what staff are planned to do what project ? and whether they actually did it or not.
The idea of the spreadsheet is that:
The user looks at "Project Plan" and decides (for example) that he needs 2 tennis players on 2nd Jan 2006. So in the "Tennis" section of that page, under "Req", he inputs 2 on the correct row (according to the date).
Now he looks at the "Worker Plan" sheet. And will select a blank cell under one of his team and allocate them to tennis ("ten") ? i have done this for two staff.
Now, in "project plan" my Sumproduct formula will return the number of staff planned for each project on each day.
So far so good. But here?s where the tricky stuff starts
When the days pass ? the staff are expected to put a code into the "worker actual" sheet if they weren?t able to do their assigned project that day. So I mark one person off as being on holiday/vacationWhat I now need the "project plan" to calculate is how many people actually were available on the day for each project. This is where i?m stuck.
As the "people actual" sheet doesn?t contain details of the project ? only the reason for absence ? how to I get a formula to cross-check what a person is working on with his reason for absence so as to deduct 1 from the total of people available for the project on "project plan"
In other words ? look at the 3 sheets, then look at "project plan". In the red cell a formula should return a 1. Because there were 2 people assigned to that project, but one of those people has an entry in the "person actual" sheet ? which means he wasn?t available that day. (Any entry in this sheet will be taken to mean that the person was unavailable that day ? otherwise it is left blank).
Please help! I?m frying my brain and Harrops don?t do Excel books.
On the attached example workbook, I am trying to track what staff are planned to do what project ? and whether they actually did it or not.
The idea of the spreadsheet is that:
The user looks at "Project Plan" and decides (for example) that he needs 2 tennis players on 2nd Jan 2006. So in the "Tennis" section of that page, under "Req", he inputs 2 on the correct row (according to the date).
Now he looks at the "Worker Plan" sheet. And will select a blank cell under one of his team and allocate them to tennis ("ten") ? i have done this for two staff.
Now, in "project plan" my Sumproduct formula will return the number of staff planned for each project on each day.
So far so good. But here?s where the tricky stuff starts
When the days pass ? the staff are expected to put a code into the "worker actual" sheet if they weren?t able to do their assigned project that day. So I mark one person off as being on holiday/vacationWhat I now need the "project plan" to calculate is how many people actually were available on the day for each project. This is where i?m stuck.
As the "people actual" sheet doesn?t contain details of the project ? only the reason for absence ? how to I get a formula to cross-check what a person is working on with his reason for absence so as to deduct 1 from the total of people available for the project on "project plan"
In other words ? look at the 3 sheets, then look at "project plan". In the red cell a formula should return a 1. Because there were 2 people assigned to that project, but one of those people has an entry in the "person actual" sheet ? which means he wasn?t available that day. (Any entry in this sheet will be taken to mean that the person was unavailable that day ? otherwise it is left blank).
Please help! I?m frying my brain and Harrops don?t do Excel books.