Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 7
Default 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
Attached Images
  
  #2   Report Post  
Senior Member
 
Location: Philippines
Posts: 161
Default

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.
Attached Files
File Type: zip DTL.zip (8.0 KB, 81 views)
  #3   Report Post  
Junior Member
 
Posts: 7
Thumbs up

Quote:
Originally Posted by wickedchew View Post
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.
This is what I was looking for, with a slight tweak though. Is there any way to have the cells in worksheet DTL 2 J6-20 auto populate into the E6-20 cells? When I change the info in the J6-20 cells nothing happens on the E 6-20 cells with the exception on changing of the names.

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   Report Post  
Senior Member
 
Location: Philippines
Posts: 161
Default

Quote:
Originally Posted by scubadivingfool View Post
This is what I was looking for, with a slight tweak though. Is there any way to have the cells in worksheet DTL 2 J6-20 auto populate into the E6-20 cells? When I change the info in the J6-20 cells nothing happens on the E 6-20 cells with the exception on changing of the names.

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
Once you changed the names on column J, just change the name on E6 and the rest will follow.

The formula is quite hard to explain though... You can attach your template and I'll do it for you. ^_^
  #5   Report Post  
Junior Member
 
Posts: 7
Default

Quote:
Originally Posted by wickedchew View Post
Once you changed the names on column J, just change the name on E6 and the rest will follow.

The formula is quite hard to explain though... You can attach your template and I'll do it for you. ^_^
OK, I have added the template. As you can tell, each day of the week are in each worksheet as well as a worksheet to do the timing inputs.

Thanks for all the help
Attached Files
File Type: zip testDTL.zip (79.8 KB, 77 views)


  #6   Report Post  
Senior Member
 
Location: Philippines
Posts: 161
Default

Quote:
Originally Posted by scubadivingfool View Post
OK, I have added the template. As you can tell, each day of the week are in each worksheet as well as a worksheet to do the timing inputs.

Thanks for all the help
Here's your template.

I have included some instructions on how to use the template.
Attached Files
File Type: zip DTL.zip (95.2 KB, 75 views)
  #7   Report Post  
Junior Member
 
Posts: 7
Default

Quote:
Originally Posted by wickedchew View Post
Here's your template.

I have included some instructions on how to use the template.
This is exactly what I was looking for. Thanks

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   Report Post  
Senior Member
 
Location: Philippines
Posts: 161
Default

Quote:
Originally Posted by scubadivingfool View Post
This is exactly what I was looking for. Thanks

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
Have you tried changing the value in G10?

Each work day sheet has it's own corresponding column in the times worksheet.
  #9   Report Post  
Junior Member
 
Posts: 7
Default

Quote:
Originally Posted by wickedchew View Post
Have you tried changing the value in G10?

Each work day sheet has it's own corresponding column in the times worksheet.
That I have. On Monday if I have George set to OPEN in the TIMES worksheet, on the Monday worksheet George get set to MID.

Also can you have the drop down selection digger please.

Thanks againtype
  #10   Report Post  
Senior Member
 
Location: Philippines
Posts: 161
Default

Quote:
Originally Posted by scubadivingfool View Post
That I have. On Monday if I have George set to OPEN in the TIMES worksheet, on the Monday worksheet George get set to MID.

Also can you have the drop down selection digger please.

Thanks againtype
I have corrected the error. Sorry about that. It was looking at a different table.

The only way that you can make the selection bigger is that when the zoom is set to 100% instead of 55%.
Attached Files
File Type: zip DTL.zip (95.8 KB, 66 views)


  #11   Report Post  
Junior Member
 
Posts: 7
Default

Quote:
Originally Posted by wickedchew View Post
I have corrected the error. Sorry about that. It was looking at a different table.

The only way that you can make the selection bigger is that when the zoom is set to 100% instead of 55%.
Sorry that didn't work. I am trying to put in the monday schedule as follows within the TIMES worksheet

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   Report Post  
Senior Member
 
Location: Philippines
Posts: 161
Unhappy

Quote:
Originally Posted by scubadivingfool View Post
Sorry that didn't work. I am trying to put in the monday schedule as follows within the TIMES worksheet

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
I got your point. My bad....

Formula changed in each work week. You may receive Circular Formula errors. Just change the name on G10.
Attached Files
File Type: zip DTL.zip (94.3 KB, 55 views)
  #13   Report Post  
Junior Member
 
Posts: 7
Default

Quote:
Originally Posted by wickedchew View Post
I got your point. My bad....

Formula changed in each work week. You may receive Circular Formula errors. Just change the name on G10.
98% perfect. Just one more thing. The last three duties need to be done by the people on the CLOSE and not the MID.

Thanks
  #14   Report Post  
Senior Member
 
Location: Philippines
Posts: 161
Default

Quote:
Originally Posted by scubadivingfool View Post
98% perfect. Just one more thing. The last three duties need to be done by the people on the CLOSE and not the MID.

Thanks
Do this for each worksheet for cells G36, G37 and G38

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert lines in more than one worksheet at a time... Marvin Excel Worksheet Functions 3 August 22nd 07 08:36 PM
Insert Rows Based on values in another worksheet Jason Excel Discussion (Misc queries) 3 June 24th 07 01:13 PM
insert data based on time BrianMcC Excel Discussion (Misc queries) 2 March 31st 07 02:02 PM
Excel to recalculate a worksheet based on a time interval HeireneM Excel Worksheet Functions 5 March 7th 07 04:12 PM
How do I insert rows in more than one worksheet at a time? Ohio accountant Excel Worksheet Functions 1 October 13th 06 10:14 PM


All times are GMT +1. The time now is 03:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"