Comparing Multiple Sets of Data and Returning Appropriate Cell

[boyko59]

Hello,
New guy here. Just joined now to post this.
Here's my problem that I'm sure one of you Excel Guru's can figure out...
I got 4 Sheets in this Excel Book: Time Sheet, PCA Time Sheet, 24HR Staffing, Resources
I am trying to use a drop-down box to select from the available range of dates on the 24HR Staffing sheet and check it across multiple columns of the Time Sheet sheet. Once it finds a match, stop and search that column for the second criteria.
The second criteria is the shift worked. If any of the cells in the date column match any of the shifts that fall within the given time frame, return the employees name from that row. If they check the row and it doesn't match, check the next row and continue to try and match those two criteria.

If I was not clear enough, please let me know and I will do my best to clarify anything. I believe the attachment of the book worked, if not, I will try again after posting.

If we solve this issue, I will have one other one in this book, that is very similar.

The most recent somewhat functioning attempt I've created is: {=IF(AND(OR('Time Sheet'!B4=Resources!E2,'Time Sheet'!B4=Resources!E3))*NDate='Time Sheet'!B3,EmpName,FALSE)} ----- but that does not cycle through the rows and columns as needed.

Thank you very much for any help you can provide.

Note: Unfortunately, I do not know VBA, so I have not gone that route.

[ChemistB]

I wasn't sure where you wanted this formula. I put it in A6 of 24 Hr Staffing, copied down

It is an ARRAYED Formula, in A6

=IFERROR(INDEX('Time Sheet'!$A$4:$A$32, SMALL(IF(INDEX('Time Sheet'!$B$4:$AC$32,,MATCH(NDate,NurseDate,0))=$A$4, ROW($A$4:$A$32)-ROW($A$4)+1), ROWS($A$6:$A6))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[boyko59]

You're amazing, ChemistB. If you don't mind, I have one other issue I'm trying to do. If you have time, I'll explain it. It should be very similar.

[boyko59]

So, I guess here are the other questions.... whomever has time.

Here is the Breakdown with the shifts that fall within each breakdown:
BD | Shifts that fall within BD
7A-3 | 7A-3 , 7A-7
3P-7 | 7A-7 , 11A-11 , 3P-11
7P-11 | 11A-11 , 3P-11 , 7P-11
11P-7 | 7P-7 , 11P-7

So, how do I make it so that for example, if Amanda works 7A-7, she falls in 3P-7 block without the actual shift name matching? The solution provided will fill in that block if the Shift Breakdown matches the Shift Exactly, but not if it's in the middle of it. --- I hope that made sense.

The other issue is very similar to that ---
If someone works a certain shift, I need to populate the Code section accordingly.
Shift | Code
7A-3 | 7
7A-7 | 7A
11A-11 | 11A
3P-11 | 3
7P-7 | 7P
11P-7 | 11

So, if Amanda does work, and she works the 7A-7 shift, her code needs to populate with the code 7A in both sections (7A-3 and 3P-7)


Thanks again for any help. If you need any additional information for clarification, I'd be more than happy to provide it.

[boyko59]

I apologize for not clarifying that on the original request.

[boyko59]

Bump.

If anyone looking at this needs more info, please let me know.