I GOTTA MONSTER TO DEAL WITH
Posted by kac on August 29, 2001 11:43 PM
Worksheet 1 THis is an example, real sheet is larger.
A
Name
John Smith
Jill Jones
Will Stevens
B
Level
1
2
3
c
Date(8/30/01)
Under date I have times for when the employees will work such as
7a to 3p
3p to 7p
7p to 11p
Date also takes up columns D-Z
On worksheet 2 I need the names to pop up where they are needed
I would type in the date in A1
B1 would be headed level and B2 & B3 would = 1
B4 & B5 =2 and B5 & B6 =3
c1 would has time 7a to 3p, d1 has time 3p to 7p, and e1 has time 7p to 11p
in c2-6, d2-d6 , & e2-e6 I need a formula that will put the employees name in the cell if they match the qualifications
please help
Posted by Don on August 30, 2001 5:48 AM
I can think of a way to use VLOOKUP, but it would pick the same names every time (first occurrance of a level-time combination) so that would not work.
Consider working the other way round where you assign the names and have Worksheet 2 count the slots so you know how many positions you have filled (or conversely how many you have yet to fill).
To do this, in worksheet 1 I'd have something like this setup: A is name, B is Level, C is time available, the D is a combination of Level and time (=B2&"-"&C2 where the dash is cosmetic and has no function).
In worksheet 2, Col A enter the names you are considering for a shift, Col B can be a vlookup, pulling in the column D information.
Then in Worksheet 3 or elsewhere on WKST 2, make a list of possible combinations (1-3-7PM, 2-3-7PM, etc) and beside the list do some COUNTIFs to see how many positions you've filled. You still have to make the assignments by hand, but at least the spreadsheet can tell you how close you are to being finished making them.
Don
Posted by Eric on August 30, 2001 6:28 AM
If this isn't Kris, then it's the exact same problem as Kris proposed earlier on the board
Other posters should check out Mark W. and my replies to Kris's earlier posts to get a head start on this problem.
If this is Kris- sorry I couldn't help; if this isn't Kris, then check out those earlier posts to see if they help.
