Get names from another workbook based on name/date

[simont485]

I have 2 workbooks that are used for staff rotas (A & B). In both Rotas the layout is shown as: names in Column A A2:A7 dates in Row 1 A2 :NL2. Shifts are shown as D, E, M or N

In Rota A there is a cell to input a date and the rota scrolls across to that date.

I would like it to then show the names of staff on shift for the same date from Rota B.

[oeldere]

B12 =INDEX(B!$B$3:$G$14,MATCH(A!B1,B!$B$3:$B$13,0),MATCH(A!B2,B!$B$3:$G$3,0))

See the attached file.

[simont485]

OK thank you for that but that isn't quite what I was looking for.

In Cell B1 enter a Name from Name 1 to Name 5. In Cell B1 enter a date.

In B12:B21 I would expect to see Name 6 to Name 16 from sheet B

Example:

Cell B1 enter Name 1, Cell B2 1/4/20

In B12:B21 I would want to see Name 6, Name 9, Name 12 and Name 14 from sheet B

Many thanks

[JeteMc]

I am not sure that I understand what role the name in cell B1 plays however based on the expected results given in post #3, try the following:

Please Login or Register  to view this content.

Let us know if you have any questions.

[simont485]

Thank you....yes good idea to ignore the name input!!

So yes it does wok.....is it possible to add a little change?

Is it possible to ignore not only the blank cells but also if a cell has text that starts with the letter H. ie has H as its first character?

H indicates holiday so would not be at work on shift on that dat.

Many thanks in advance

[JeteMc]

Try the following:

Please Login or Register  to view this content.

Let us know if you have any questions.

[simont485]

OK that works thank you!!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.