VLOOKUP Help

[clayw]

I need a little help.. I am trying to get s excel assignment sheet setup, to pull peoples names off another sheet depending on the day of the week.. I have two tabs, 1. Assignment Sheet, 2. Data

1. Names/Days the work in DATA Tab.

2. Assignment Sheets Tab (1st = 1=, 2nd = 2, etc etc) have a slot where you can inputer the day of the week. (mon, tues etc)

3. Depending on the day of the week selected, the employee names/times
working would be pulled off the DATA tab and placed in the names catorogy.

Ex of my Data Sheet

Monday
Employee#1 8:00 - 10:00
Employee#2 9:00 - 11:00

Tuesday
Employee#3 9:00 - 11:00
Employee#4 10:00 - 11:00
Employee#5 11:00 - 11:00




Assignment Sheet:

Enter Day of the Week : ----------

Names:
(Depending on the day on the week would pull employees from DATA tab.)


THANKS GUYS! Any help would be great!!

[Domenic]

Try the following...

On your Data sheet, first change all cells that contain the day of the week as follows...

Change the value of the cell containing 'Monday' to 1/3/2005 and custom format the cell as dddd

Change the value of the cell containing 'Tuesday' to 1/4/2005 and custom format the cell as dddd

...and continue for all days of the week.

On your Assignment sheet...

A2, copied down:

=IF(Data!B2<>"",CHOOSE(WEEKDAY(INDEX(Data!$A$1:A2,MATCH(9.99999999999999E+307,Data!$A$1:A2)),2),"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"),"")

B1: enter the day of interest, for example Tuesday

C1: enter a 0 (zero)

C2, copied down:

=IF(A2=$B$1,LOOKUP(9.99999999999999E+307,$C$1:C1)+1,"")

D1:

=LOOKUP(9.99999999999999E+307,C:C)

E2, copied down:

=IF(ROW()-ROW(E$2)+1<=$D$1,MATCH(ROW()-ROW(E$2)+1,$C$2:$C$8,0),"")

F2, copied down and to the next column:

=IF(N($E2),INDEX(Data!A$2:A$8,$E2),"")

If you wish, you can then hide any helper column.

Hope this helps!

I need a little help.. I am trying to get s excel assignment sheet setup, to pull peoples names off another sheet depending on the day of the week.. I have two tabs, 1. Assignment Sheet, 2. Data

1. Names/Days the work in DATA Tab.

2. Assignment Sheets Tab (1st = 1=, 2nd = 2, etc etc) have a slot where you can inputer the day of the week. (mon, tues etc)

3. Depending on the day of the week selected, the employee names/times
working would be pulled off the DATA tab and placed in the names catorogy.

Ex of my Data Sheet

Monday
Employee#1 8:00 - 10:00
Employee#2 9:00 - 11:00

Tuesday
Employee#3 9:00 - 11:00
Employee#4 10:00 - 11:00
Employee#5 11:00 - 11:00




Assignment Sheet:

Enter Day of the Week : ----------

Names:
(Depending on the day on the week would pull employees from DATA tab.)


THANKS GUYS! Any help would be great!!