Jun 29 2023 06:53 PM
Jun 29 2023 06:53 PM
I have a formula which matches a date on a roster to check if someone is on shift and display their name on another sheet. Cell B2 displays =TODAY() so it should automatically count up the roster, the idea is to have a live display of who is on shift. The roster is based on colour so I have a VBA script which assigns a value to different colours. In this case a value of 3 is a day shift. So this formula matches the date in cell B2 with a date from row 3 on sheet1 and checks the corresponding cell in row 9 for a value of 3, if this is true it displays the corresponding cell of column D.
=IF(AND(COUNTIF(Sheet1!E3:NN3,B2)>0, INDEX(Sheet1!E9:NN9, MATCH(B2, Sheet1!E3:NN3, 0))=3), Sheet1!D9, "")
This is fine for saying 'is "x" on shift, yes or no' and i can use this for the single supervisor on shift. But I need to either edit this formula or use a different array in order to do the same thing for multiple employees. I need to match cell B2 with a cell from row 3 and then find a corresponding cell from the same column from rows 10-17 with a value of 3 and display the corresponding cell from column D. I've tried a couple of arrays e.g
=IF(INDEX(Sheet1!10:17, MATCH(B2, Sheet1!E3:NN3, 0), COLUMN($A$1))=3, INDEX(D10:17, MATCH(B2, Sheet1!E3:NN3,0)), "")
but I can't seem to get anything to work. Any help would be greatly appreciated.
Jun 29 2023 10:32 PM
To display the names of multiple employees who are on shift based on your criteria, you can modify the formula using array formulas.
Here is a suggested solution:
=IFERROR(INDEX(Sheet1!$D$10:$D$17, SMALL(IF((Sheet1!$E$9:$NN$9=3)*(Sheet1!$E$10:$NN$17<>""), ROW(Sheet1!$E$10:$NN$17)-ROW(Sheet1!$E$10)+1), ROW(A1))), "")
This formula should be entered as an array formula by pressing Ctrl+Shift+Enter. Then you can drag it down to populate the names of multiple employees who are on shift.
Here's how the formula works:
Make sure to adjust the ranges (Sheet1!$D$10:$D$17, Sheet1!$E$9:$NN$9, and Sheet1!$E$10:$NN$17) according to your actual data layout.
By using this formula, you should be able to display the names of multiple employees who have a value of 3 in the corresponding cell of rows 10-17 when matching with the date in cell B2.
The texts, steps and functions were created with the help of AI for reasons of time.
Hope this will help you.