On-Call Schedule: Table Needs to Show Dates

egarcia7

New Member
Joined
Aug 9, 2016
Messages
17
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. December
[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Position[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]Hawk[/TD]
[TD]Coordinator[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rudy[/TD]
[TD]Coordinator[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ryan[/TD]
[TD]Coordinator[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stephon[/TD]
[TD]Coordinator[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]Coordinator[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[TD]CO[/TD]
[/TR]
</tbody>[/TABLE]


  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.

[TABLE="width: 0"]
<tbody>[TR]
[TD]Name:[/TD]
[TD="colspan: 2"]Hawk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 14"][/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]Day 1[/TD]
[TD]Day 2[/TD]
[TD]Day 3[/TD]
[TD]Day 4[/TD]
[TD]Day 5[/TD]
[TD]Day 6[/TD]
[TD]Day 7[/TD]
[TD]Day 8[/TD]
[TD]Day 9[/TD]
[TD]Day 10[/TD]
[TD]Day 11[/TD]
[TD]Day 12[/TD]
[TD]Day 13[/TD]
[TD]Day 14[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]October[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]November[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]December[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
ABCDEFGHIJKLMNOP
1FriSatSunMonTueWedThuFriSatSunMonTueWedThu
2NamePosition1234567891011121314
3HawkCoordinatorCOCOCOCOCOCOCO
4RudyCoordinatorCOCOCOCOCOCOCO
5RyanCoordinator
6StephonCoordinator
7TimCoordinator
March




Book1
ABCDEFGHIJKLMNO
2Name:Hawk
3
4PositionCO
5
6MonthDay 1Day 2Day 3Day 4Day 5Day 6Day 7Day 8Day 9Day 10Day 11Day 12Day 13Day 14
7January
8February
9March1234567
10April
11May
12June
13July
14August
15September
16October
17November
18December
Overview
Cell Formulas
RangeFormula
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.
 
Upvote 0
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
 
Upvote 0
Thanks for the feedback, hopefully it's what you're after.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,249
Members
452,623
Latest member
Techenthusiast

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top