Insert from different worksheet based on time
2 Attachment(s)
OK so here is my problem that I hope you can help with. At work we have what is called a “daily task List (DTL)” for the employees to do throughout there shift with each DTL being different on each day of the week.
What I am trying to do is load is input everything into a timing worksheet (the image in yellow) and have is auto-populate correctly into each of the seven different worksheet. Let me explain in more detail below: Ryan, Moe and Nick, who are opening need to be inserted into the yellow highlighted area. AS you can see though there are 5 spots so I need the inserting to go back to the beginning and start at the top of the openers. I then need the orange to be filled with both Openers and Mids while the green needs to be filled with Mids and Closers. I can send the excel spreadsheet if you need it. Also if you need more clarification please ask Thanks David |
1 Attachment(s)
There are 2 worksheets in this attachment.
The first sheet, you have to select a name from column C. The drop-down is dependent on the number of people per shift. YELLOW = OPENING STAFFS ORANGE = OPENING AND MID STAFFS GREEN = CLOSING STAFFS The second sheet does it automatically. You just need to select an employee E6 and the rest will populate. |
Quote:
Also can you tell me how to edit the code you used? I have to place this into a pre-existing template. It needs to look exactly like the first attached image. Thanks for all your hard work, very much appreciated |
Quote:
The formula is quite hard to explain though... You can attach your template and I'll do it for you. ^_^ |
1 Attachment(s)
Quote:
Thanks for all the help |
1 Attachment(s)
Quote:
I have included some instructions on how to use the template. |
Quote:
There are some errors however on the template. When I change the names of the people in the times worksheet, it is not changing on the weekday sheet Thanks |
Quote:
Each work day sheet has it's own corresponding column in the times worksheet. |
Quote:
Also can you have the drop down selection digger please. Thanks againtype |
1 Attachment(s)
Quote:
The only way that you can make the selection bigger is that when the zoom is set to 100% instead of 55%. |
Quote:
Nick L OPEN George OPEN Aaron OPEN Nick S MID Elwin CLOSE Wayne CLOSE What I get in the Monday worksheet under the todays schedule is the following Nick L OPEN Wayne CLOSE David #N/A Moe #N/A George OPEN Elwin CLOSE Nick S MID Peter #N/A When Nick L is selected under the owner tab he is populated in every cell Thanks |
1 Attachment(s)
Quote:
Formula changed in each work week. You may receive Circular Formula errors. Just change the name on G10. |
Quote:
Thanks |
Quote:
Replace the word MID with CLOSE BEFORE =IF(B37="","",IF(IF(VLOOKUP(OFFSET($AB$11,MATCH(IN DEX($G$30:$G$38,MATCH(MAX($AE$30:AE36),$AE$30:AE36 ,0)),$AB$11:$AB$28,0),0,1,1),$AB$11:$AC$28,2,0)="O pen",OFFSET($AB$11,MATCH("Mid",$AC$11:$AC$28,0),0, 1,1),OFFSET($AB$11,MATCH(INDEX($G$30:$G$38,MATCH(M AX($AE$30:AE36),$AE$30:AE36,0)),$AB$11:$AB$28,0),0 ,1,1))="",OFFSET($AB$11,MATCH("Mid",$AC$11:$AC$28, 0)-1,0,1,1),IF(VLOOKUP(OFFSET($AB$11,MATCH(INDEX($G$3 0:$G$38,MATCH(MAX($AE$30:AE36),$AE$30:AE36,0)),$AB $11:$AB$28,0),0,1,1),$AB$11:$AC$28,2,0)="Open",OFF SET($AB$11,MATCH("Mid",$AC$11:$AC$28,0),0,1,1),OFF SET($AB$11,MATCH(INDEX($G$30:$G$38,MATCH(MAX($AE$3 0:AE36),$AE$30:AE36,0)),$AB$11:$AB$28,0),0,1,1)))) AFTER =IF(B37="","",IF(IF(VLOOKUP(OFFSET($AB$11,MATCH(IN DEX($G$30:$G$38,MATCH(MAX($AE$30:AE36),$AE$30:AE36 ,0)),$AB$11:$AB$28,0),0,1,1),$AB$11:$AC$28,2,0)="O pen",OFFSET($AB$11,MATCH("Close",$AC$11:$AC$28,0), 0,1,1),OFFSET($AB$11,MATCH(INDEX($G$30:$G$38,MATCH (MAX($AE$30:AE36),$AE$30:AE36,0)),$AB$11:$AB$28,0) ,0,1,1))="",OFFSET($AB$11,MATCH("Close",$AC$11:$AC $28,0)-1,0,1,1),IF(VLOOKUP(OFFSET($AB$11,MATCH(INDEX($G$3 0:$G$38,MATCH(MAX($AE$30:AE36),$AE$30:AE36,0)),$AB $11:$AB$28,0),0,1,1),$AB$11:$AC$28,2,0)="Open",OFF SET($AB$11,MATCH("Close",$AC$11:$AC$28,0),0,1,1),O FFSET($AB$11,MATCH(INDEX($G$30:$G$38,MATCH(MAX($AE $30:AE36),$AE$30:AE36,0)),$AB$11:$AB$28,0),0,1,1)) )) |
All times are GMT +1. The time now is 06:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com