1

I am trying to create a formula that will automatically tell me which crew is working on a particular day and shift. I have tried several things from many different forums but somehow I am not doing something correct.

This worksheet is very large and I do not want to create new tables or additional columns if at all possible.

enter image description here

Final Product Example

2
  • Can you post a data sample WITH the crews in the DATA WS and then post what you think the result should be? There are 5 lines for each date/time combo. Only one crew can be returned. What are you looking for?
    – teylyn
    Dec 29, 2015 at 20:41
  • This worksheet has 98 columns. I am looking for the crew to repeat itself 5 times. I run pivot tables off the data and this detail allows me to filter by crew, shift, date, and the data that is in the other columns. I will add an image of the final product I am looking to get with a formula that I can copy down. Right now I manually type in the crews.
    – K.Mack
    Dec 30, 2015 at 12:17

2 Answers 2

0

You can use the facts that your source data has two lines for each date and the second line is always PM to make this easy.

=INDEX(SourceSheetName!$C$2:$C$11,MATCH(A2,SourceSheetName!$A$2:$A$11,0)+IF(D2="PM",1,0))

This looks up the date in the table and returns the first Crew for that date. If the shift is PM, it will return the second record for that date.

4
  • It sounds like the right idea, however it returned a #N/A result. =INDEX(Schedule!$C$2:$C$733,MATCH(A112,Schedule!$A$2:$A$733,0)+IF(D112="PM",1,0))
    – K.Mack
    Dec 30, 2015 at 12:26
  • The date column in the DATA worksheet is a formula. Could that be causing the issues?
    – K.Mack
    Dec 30, 2015 at 12:30
  • @Crainiac Does it fail just for that one record? Maybe use the Evaluate Formula tool in Excel to step into the formula and see exactly where it's going wrong.
    – Excellll
    Dec 30, 2015 at 16:16
  • I have never used that function. It appears that the Match part is what is causing the issue. It turns out that I had a date & time (time not showing) in the date column on the Schedule worksheet. I removed that and then the formula worked great!! Thank you so much for your help!!
    – K.Mack
    Dec 30, 2015 at 17:18
0

What you describe can easily be done with a pivot table. Select the data in the first sheet (or turn it into an Excel Table with Insert > Table), then click Insert > Pivot table.

In the Pivot panel, drag Day, Shifts and Crews into the rows pane and the Lines into the Filters pane. On the Design ribbon of the Pivot Tools, set the pivot table to tabular format and repeat all item labels. These two commands are in the Report Layout dropdown.

Turn off Subtotals and Grand totals.

Select a line in the filters drop down above the pivot table. In the screenshot below, the "All lines" value has been selected.

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .