• 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


  • 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

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



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


Well-Known Member
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.