Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Insert from different worksheet based on time
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 |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 Last edited by scubadivingfool : August 2nd 10 at 02:57 PM Reason: more explanation |
#4
|
|||
|
|||
Quote:
The formula is quite hard to explain though... You can attach your template and I'll do it for you. ^_^ |
#5
|
|||
|
|||
Quote:
Thanks for all the help |
#6
|
|||
|
|||
Quote:
I have included some instructions on how to use the template. |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
Quote:
Each work day sheet has it's own corresponding column in the times worksheet. |
#9
|
|||
|
|||
Quote:
Also can you have the drop down selection digger please. Thanks againtype |
#10
|
|||
|
|||
Quote:
The only way that you can make the selection bigger is that when the zoom is set to 100% instead of 55%. |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Quote:
Formula changed in each work week. You may receive Circular Formula errors. Just change the name on G10. |
#13
|
|||
|
|||
Quote:
Thanks |
#14
|
|||
|
|||
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)) )) Last edited by wickedchew : August 4th 10 at 06:03 PM Reason: Ugly formula, donchatink?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert lines in more than one worksheet at a time... | Excel Worksheet Functions | |||
Insert Rows Based on values in another worksheet | Excel Discussion (Misc queries) | |||
insert data based on time | Excel Discussion (Misc queries) | |||
Excel to recalculate a worksheet based on a time interval | Excel Worksheet Functions | |||
How do I insert rows in more than one worksheet at a time? | Excel Worksheet Functions |