I hope I can explain this clearly.
I have 3 Project Managers that work for me. Each are assigned jobs as we start them. So we have 11 jobs open, I've assigned 4 to two of my PMs and 3 to the other PM which makes a total of 11 jobs assigned
So the crux of the question is this. I want to have a quick overview of how many jobs at any given time are open and how many each PM looks after.
My layout is going to be simple
Project Manager | Total Jobs Assigned
John 4
Harry 4
David 3
So each time I open a job (or my admin does), we enter the job code, start date and then assign the PM. So it has to be something if I enter for example 'David', it adds to David's tally.
Then I also want a separate area where I'll name a column 'Total Jobs Open' and it adds to that too.
So this should be fairly simple so far.
But it also has to do this. When we close down a job under 'Completion Date' (another column I have), I want it to subtract from the relevant PM again. So if David finishes a job, he should be down to '2' jobs. So the formula have to keep an eye on the 'Completion Date' column and subtract from the relevant contractor once the completion date is entered.
I hope this is clear. If not, I can try and knock up an UI layout to better explain.
I have 3 Project Managers that work for me. Each are assigned jobs as we start them. So we have 11 jobs open, I've assigned 4 to two of my PMs and 3 to the other PM which makes a total of 11 jobs assigned
So the crux of the question is this. I want to have a quick overview of how many jobs at any given time are open and how many each PM looks after.
My layout is going to be simple
Project Manager | Total Jobs Assigned
John 4
Harry 4
David 3
So each time I open a job (or my admin does), we enter the job code, start date and then assign the PM. So it has to be something if I enter for example 'David', it adds to David's tally.
Then I also want a separate area where I'll name a column 'Total Jobs Open' and it adds to that too.
So this should be fairly simple so far.
But it also has to do this. When we close down a job under 'Completion Date' (another column I have), I want it to subtract from the relevant PM again. So if David finishes a job, he should be down to '2' jobs. So the formula have to keep an eye on the 'Completion Date' column and subtract from the relevant contractor once the completion date is entered.
I hope this is clear. If not, I can try and knock up an UI layout to better explain.
