populate data from table to rows based on column cell values

[hossam sadek]

Hi
i need help with the attached sheet,i want to have data populated from sheet "Shifts" to Sheet " Working Schedule " based on the day starting from cell B2 in Sheet " Working Schedule " to be matching agent name and the day

 

[Belleke]

You don't have dates in your shifts sheet so without dates your question can't be solved.

 

[p45cal]

Since your data is in such an awful arrangement on the Shifts sheet, the formula is a long one just to get the shift start to shift end times.
You must first give the range A1:X27 on the Shifts sheet a name myData (this shortens the formula(!)).
Then in cell B2 of the Working Schedule sheet you have this formula:

=TEXT(INDEX(OFFSET(INDEX(myData,SUMPRODUCT((myData=$A2)*ROW(myData)),SUMPRODUCT((myData=$A2)*COLUMN(myData))),1,0,8,3),MATCH(TEXT(B$1,"dddd"),INDEX(OFFSET(INDEX(myData,SUMPRODUCT((myData=$A2)*ROW(myData)),SUMPRODUCT((myData=$A2)*COLUMN(myData))),1,0,8,3),0,1),0),2),"h:mm AM/PM") & " - " & TEXT(INDEX(OFFSET(INDEX(myData,SUMPRODUCT((myData=$A2)*ROW(myData)),SUMPRODUCT((myData=$A2)*COLUMN(myData))),1,0,8,3),MATCH(TEXT(B$1,"dddd"),INDEX(OFFSET(INDEX(myData,SUMPRODUCT((myData=$A2)*ROW(myData)),SUMPRODUCT((myData=$A2)*COLUMN(myData))),1,0,8,3),0,1),0),3),"h:mm AM/PM")

which you can copy down and across. Depending on your version of Excel, you may have to commit this formula to the sheet using Ctrl+Shift+Enter rather than the usual plain Enter. If you get an error in the cell, it's likely you need to do this.

 

[hossam sadek]

@p45cal @Belleke > Thank you guys for the help, i really appreciate it , Happy New Year to all of you and your families

 

[p45cal]

Make sure Rania I/Ebrahim is spelt the same way on both sheets!!

 

[hossam sadek]

Yes will do that