I am going to try to give an explanation of how this could be done, and also am attaching a sample of your master sheet (values only) and a couple (W607 & S120) of the Code sheets, with the formulas and conditional formatting applied.
In the Code sheets:
1) Paste the following formula in E4 and copy down as far as needed: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)A)You could hide the lines that are not yet used with a conditional formatting rule: =$A4="" Applies to =$E:$E Format > Font > no color
2) Paste the following array formula* in A4 and copy down as far as needed: =IFERROR(INDEX(Master!A$4:A$20,SMALL(IFERROR((ROW(A$1:A$20)/(Master!$E$4:$E$20=$E4)),FALSE),ROW(A1))),"")A) *Activate the formula by pressing the F2 key then press 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 All of the values in column A will be the same.
B) You'll need to format column A to Date.
3) Drag the fill handle of A4 across to D4 and then down as far as needed.A) You'll need to format column B to time.
4) Select D4 and press Ctrl + c
5) Select F4 and press Ctrl + v
6) Drag the fill handle of F4 across to H4 and down as far as needed.A) Change the format of column H to general
B) You may want to apply a conditional formatting rule to column H to hide the zeroes: CF rule =$H4=0 Applies to =$H:$H Format > Font > no color
Let me know if you have any questions.
Bookmarks