On-Call Schedule: Table Needs to Show Dates

[egarcia7]

First of all I want to thank MrExcel.com for everything you guys do, I have been able to find plenty of answers to many questions I’ve had in the past.

Well let’s get into this conundrum, in short this is an On-Call Schedule, what I’m trying to accomplish is to for the techs to select their name from a drop down (I can do this) and for the table to give them the days that they will be on-call (I can’t do this). I have tried different index, match formulas and If statements and I haven’t been able to get the dates.

1. 13 worksheets (tabs)
   1. Overview
   2. January
   3. February
   4. March
   5. …
   6. December

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Name

Position

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

Hawk

Coordinator

CO

CO

CO

CO

CO

CO

CO

Rudy

Coordinator

CO

CO

CO

CO

CO

CO

CO

Ryan

Coordinator

CO

CO

CO

CO

CO

CO

CO

Stephon

Coordinator

CO

CO

CO

CO

CO

CO

CO

Tim

Coordinator

CO

CO

CO

<tbody>
</tbody>

1. Overview worksheet
   1. This is where they select their name and the table pulls the days. As you can see above the day of the week is listed in a row and the names are on a column. But the table below when you select your name I want the day of the week appear. CO means coordinator and there are other tags I’m using (CO, MN, SU, FT, CT)
   2. So the table below for day 1 should display, 1,2,3,4,5,6,7 for the month of March.
      1. His next on-call will be in July, so everything else should be blank.
   3. On-Call lasts for 7 days straight.

Name:

Hawk

Month

Day 1

Day 2

Day 3

Day 4

Day 5

Day 6

Day 7

Day 8

Day 9

Day 10

Day 11

Day 12

Day 13

Day 14

January

#N/A

#N/A

February

#N/A

#N/A

March

#N/A

#N/A

April

#N/A

#N/A

May

June

July

August

September

October

November

December

<tbody>
</tbody>

I have unsuccessfully tried several iterations of the formulas below:

• =INDEX(January[[#Headers],[1]],MATCH(OverView!Q11,January!D10,0))
• =INDEX(January[[#Headers],[1]:[31]],MATCH(1,INDEX((OverView!Q11=January[[#All],[Name]])*("*"=January[[1]:[31]]),0,1),0))

Thank you all for your help,

Erik

 

[RasGhul]

Hi,

You could try this, you'll need to extend the ranges to suit your data. Also the formula will need to repeated to look at each tab for each month row in the Overview table.

e.g. all of the INDEX(March!$C$2:$P$2 in the formula will be Jan!, Feb!, etc for each of your month tabs. I didn't want to introduce the indirect function here as it may slow down your sheet.

I did add the Position code as a criteria("CO") if this helps so you can select name & position for your overview table.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu
2	Name	Position	1	2	3	4	5	6	7	8	9	10	11	12	13	14
3	Hawk	Coordinator	CO	CO	CO	CO	CO	CO	CO
4	Rudy	Coordinator								CO	CO	CO	CO	CO	CO	CO
5	Ryan	Coordinator
6	Stephon	Coordinator
7	Tim	Coordinator
March

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Name:	Hawk
3
4	Position	CO
5
6	Month	Day 1	Day 2	Day 3	Day 4	Day 5	Day 6	Day 7	Day 8	Day 9	Day 10	Day 11	Day 12	Day 13	Day 14
7	January
8	February
9	March	1	2	3	4	5	6	7
10	April
11	May
12	June
13	July
14	August
15	September
16	October
17	November
18	December
Overview

Cell Formulas
Range	Formula
B9	{=IFERROR(INDEX(March!$C$2:$P$2,SMALL(IF(March!$A$3:$A$7&March!$C$3:$P$7&March!$C$2:$P$2=Overview!$B$2&Overview!$B$4&COLUMNS($B$9:B9),COLUMN(March!$C$2:$P$2)-COLUMN(March!$C$2)+1),1)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[egarcia7]

RasGhul,

Thank you for your help, your idea of adding a position criteria was ingenious. I will add try this out today and I will share my outcome in the thread.

-egarcia7

 

[RasGhul]

Thanks for the feedback, hopefully it's what you're after.

 

[egarcia7]

Hello,

I have yet to make the formula work. Might there be any other option? Perhaps an index, match or index, indirect? Any help would be appreciated.

 

[RasGhul]

Hi,

Here's a link to my mock up sheet if this helps;

https://www.dropbox.com/s/awjrkrg7srn916v/On Call Schedule Table egarcia7.xlsx?dl=0

 

[egarcia7]

Hello RasGhul,

First off thank you for your help and your patience. I don't have access to Dropbox / onedrive or even gmail at work so it's been difficult.

Regardless below is an example of what the actual on-call looks like, any an all help would be appreciated.

https://1drv.ms/x/s!AhwsaU756LLRsA3kkjkOQ6wr8a9n

 

[RasGhul]

Hi egarcia,

I've put some notes into my mock-up version of your sheet. See if this suits your application, there's a lot more to be done but there is enough there to get you going hopefully;

https://www.dropbox.com/s/uwp6a6p6zo9vb3b/On_Call_Schedule_Help_JP.xlsm?dl=0

 

[egarcia7]

RasGhul,

Thank you very much this is perfect, I will be able to replicate this formula and make it function. You are amazing!

Thank you for your time, patience an expertise and have an amazing year.

-Egarcia7

 

[RasGhul]

You're welcome and thanks for the feedback