# Dynamic INDEX and MATCH?

#### apo

##### Well-known Member
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>

 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>

 Cell Formula B10 =IF(A10="", 0, VLOOKUP(A10,Employees,3,FALSE))

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Replies
6
Views
133
Replies
1
Views
173
Replies
22
Views
266
Replies
3
Views
266
Replies
10
Views
572

1,195,642
Messages
6,010,885
Members
441,571
Latest member
stolenweasel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back