Dynamic INDEX and MATCH?

[apo]

HI..

I am trying to populate B2:E2 and J2:K2 on a sheet called "Monday" with data from a sheet called "Roster". This data will populate a stacked bar chart showing workers shifts.

Currently it is using static formulas in each cell. The issue is that it needs to be dynamic because Employees can be added/removed for the Roster sheet.

I was thinking perhaps a INDEX and MATCH formula in each cell (B,C,D,E,J and K)might solve this.. but I can't seem to get my head wrapped around it.

Perhaps some VBA can do it too?

If any of the excel gurus can help, it will be most appreciated.

Thanks

Monday

*	A	B	C	D	E	F	G	H	I	J	K
1	Employee	Start	Lunch Start	Lunch End	End	Start	Time to Lunch	Time for Lunch	Time to End	First Shift	Second Shift
2	Tim	8:30 AM	12:00 PM	1:00 PM	11:00 PM	8:30 AM	3:30	1:00	10:00	TAB	TAB

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:110px;"><col style="width:64px;"><col style="width:75px;"><col style="width:69px;"><col style="width:64px;"><col style="width:64px;"><col style="width:94px;"><col style="width:101px;"><col style="width:85px;"><col style="width:90px;"><col style="width:90px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula A2 =IF(Roster!A10="", 0, VLOOKUP(Roster!A10,Employees,1,FALSE)) B2 =Roster!C10 C2 =Roster!D10 D2 =Roster!C12 E2 =Roster!D12 F2 =B2 G2 =C2-B2 H2 =D2-C2 I2 =E2-D2 J2 =Roster!C9 K2 =Roster!C11 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



Roster

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

9

*

*

TAB

TAB

Group Leader

Pokies

Change

*

Change

10

Tim

38

8:30

12:00

7:00

12:00

7:30

10:30

7:00

10:00

8:30

10:30

*

*

8:30

10:30

11

*

*

TAB

Change

Change

Pokies

Change

*

Pokies

12

*

*

13:00

23:00

13:00

18:00

11:30

15:30

11:00

14:00

11:30

14:30

*

*

11:30

13:30

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B10 =IF(A10="", 0, VLOOKUP(A10,Employees,3,FALSE)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4