• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

populate data from table to rows based on column cell values

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
 

Attachments

  • Working hours.xlsx
    24.4 KB · Views: 14
You don't have dates in your shifts sheet so without dates your question can't be solved.
 
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:
Code:
=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.
 

Attachments

  • Chandoo45561Working hours.xlsx
    39.8 KB · Views: 6
Back
Top