Excel worksheet Design Help

[wayneryeven]

Hey all
got a conundrum in excel that im not too sure how to sort!

I have a list of Jobs 1-10. I have a number of operators in a team who do these jobs. The jobs they do depends on the size of their team e.g.

3 in the team:
Person 1 does jobs 1-3, Person 2 jobs 4-7, Person 3 jobs 8-10.

4 in the team:
Person 1 does jobs 1-3, Person 2 jobs 4-6, Person 3 jobs 7-8 Person 4 jobs 9-10.

My job list remains static. What i want to be able to do is allow the user to enter the size of their team, and then the system will return the list of jobs each person has to do.

Sounds simple enough....till you start trying to lay it out in excel!

Any help greatly appreciated.

Thanks to all,

 

[xlbo]

what is the max no. of people in a team?
what is the min no. of people in a team?#
what are the rules to decide how the jobs are split when the no. of people in the team doesn't divide equally into the number of jobs?
Is the no. of jobs ALWAYS static at 10?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[wayneryeven]

hey xlbo
thanks for your reply.
a team can be from 3 to 10 people.

the jobs each person to in a team is defined by the user. it is not done automatically.
the number of jobs can change per Product but for present we assume there is only 100?

 

[xlbo]

You say in your 1st post

"My job list remains static. What i want to be able to do is allow the user to enter the size of their team, and then the system will return the list of jobs each person has to do."

that flies int eh face of your latest post

"the jobs each person to in a team is defined by the user. it is not done automatically."

If the system is to determine the list of jobs each person has to do you need to have a rule that logically determines what to do when the no. of people in the team does not equally divide into the total no. of jobs

e.g.

No. jobs = 12
Members in team = 4

Jobs per member = 3

therefore easy to split as simply allocate 3 jobs to each member

If however, no. jobs = 10

Members in team = 4

There needs to be a rule that says (as per your example) that the 1st 2 members get 3 jobs each and the 2nd 2 members get 2 jobs each...

If no. jobs = 11 and members in team = 4, how is that split out?

3,3,3,2? that is a different rule form above where it would be 3,3,2,2

What about 13 jobs and 4 members?
4,3,3,3? or 4,4,3,2? or 4,4,4,1?

Do you see what I'm getting at?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question