Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet drop down lists
I have set up a time sheet in which e6:e14 has a drop down list of times in
15 minute increments. This is the start time. I have g6:g14 with the same setup only this would be end time. Is there a way that when I enter the start time I will be able to enter the end time from the drop down list without having to scroll down from the begining? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet drop down lists
Hi!
So, there is a particular end time that corresponds to a particular start time? If so, why not just use a lookup formula? Biff "Steve" wrote in message ... I have set up a time sheet in which e6:e14 has a drop down list of times in 15 minute increments. This is the start time. I have g6:g14 with the same setup only this would be end time. Is there a way that when I enter the start time I will be able to enter the end time from the drop down list without having to scroll down from the begining? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet drop down lists
Hmmm....
After re-reading your post about 10 more times, I think I misunderstood the first time around. Why not just create separate lists. One that only contains the possible start times and another that only contains the possible end times. Biff "Biff" wrote in message ... Hi! So, there is a particular end time that corresponds to a particular start time? If so, why not just use a lookup formula? Biff "Steve" wrote in message ... I have set up a time sheet in which e6:e14 has a drop down list of times in 15 minute increments. This is the start time. I have g6:g14 with the same setup only this would be end time. Is there a way that when I enter the start time I will be able to enter the end time from the drop down list without having to scroll down from the begining? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet drop down lists
Hey Biff,
So much for being clear. Let me try again. I need to enter a start time in the first column. Then when I enter the end time I would like to be lazy and not half to scroll from the top of my time list shown in the drop down to a time after the start time. Example: 1st column start time 3:15 pm 2nd column end time 3:30 pm or later "Biff" wrote: Hmmm.... After re-reading your post about 10 more times, I think I misunderstood the first time around. Why not just create separate lists. One that only contains the possible start times and another that only contains the possible end times. Biff "Biff" wrote in message ... Hi! So, there is a particular end time that corresponds to a particular start time? If so, why not just use a lookup formula? Biff "Steve" wrote in message ... I have set up a time sheet in which e6:e14 has a drop down list of times in 15 minute increments. This is the start time. I have g6:g14 with the same setup only this would be end time. Is there a way that when I enter the start time I will be able to enter the end time from the drop down list without having to scroll down from the begining? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet drop down lists
I would like to be lazy and not half to scroll from the top
I understood that much! Let me play around with an idea I have. Biff "Steve" wrote in message ... Hey Biff, So much for being clear. Let me try again. I need to enter a start time in the first column. Then when I enter the end time I would like to be lazy and not half to scroll from the top of my time list shown in the drop down to a time after the start time. Example: 1st column start time 3:15 pm 2nd column end time 3:30 pm or later "Biff" wrote: Hmmm.... After re-reading your post about 10 more times, I think I misunderstood the first time around. Why not just create separate lists. One that only contains the possible start times and another that only contains the possible end times. Biff "Biff" wrote in message ... Hi! So, there is a particular end time that corresponds to a particular start time? If so, why not just use a lookup formula? Biff "Steve" wrote in message ... I have set up a time sheet in which e6:e14 has a drop down list of times in 15 minute increments. This is the start time. I have g6:g14 with the same setup only this would be end time. Is there a way that when I enter the start time I will be able to enter the end time from the drop down list without having to scroll down from the begining? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet drop down lists
OK, try this:
Assume you have a list of times in 15m increments in the range G1:G20. This list is the source for your drop downs in A2 (start time) and B2 (end time). As the source for the drop down in B2 use this: =OFFSET(G1,MATCH(A2,G1:G20,0),,COUNT(G1:G20)-MATCH(A2,G1:G20,0)) So, if you select 9:00 AM as the start time from the drop down in cell A2 the drop down for cell B2 will start at 9:15 AM. Biff "Biff" wrote in message ... I would like to be lazy and not half to scroll from the top I understood that much! Let me play around with an idea I have. Biff "Steve" wrote in message ... Hey Biff, So much for being clear. Let me try again. I need to enter a start time in the first column. Then when I enter the end time I would like to be lazy and not half to scroll from the top of my time list shown in the drop down to a time after the start time. Example: 1st column start time 3:15 pm 2nd column end time 3:30 pm or later "Biff" wrote: Hmmm.... After re-reading your post about 10 more times, I think I misunderstood the first time around. Why not just create separate lists. One that only contains the possible start times and another that only contains the possible end times. Biff "Biff" wrote in message ... Hi! So, there is a particular end time that corresponds to a particular start time? If so, why not just use a lookup formula? Biff "Steve" wrote in message ... I have set up a time sheet in which e6:e14 has a drop down list of times in 15 minute increments. This is the start time. I have g6:g14 with the same setup only this would be end time. Is there a way that when I enter the start time I will be able to enter the end time from the drop down list without having to scroll down from the begining? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet drop down lists
We are getting close. This formula works great unless there is a drop down
list, which I still need. When this formula is entered into a cell with a drop down list I get a #value error. Any suggestions? "Biff" wrote: OK, try this: Assume you have a list of times in 15m increments in the range G1:G20. This list is the source for your drop downs in A2 (start time) and B2 (end time). As the source for the drop down in B2 use this: =OFFSET(G1,MATCH(A2,G1:G20,0),,COUNT(G1:G20)-MATCH(A2,G1:G20,0)) So, if you select 9:00 AM as the start time from the drop down in cell A2 the drop down for cell B2 will start at 9:15 AM. Biff "Biff" wrote in message ... I would like to be lazy and not half to scroll from the top I understood that much! Let me play around with an idea I have. Biff "Steve" wrote in message ... Hey Biff, So much for being clear. Let me try again. I need to enter a start time in the first column. Then when I enter the end time I would like to be lazy and not half to scroll from the top of my time list shown in the drop down to a time after the start time. Example: 1st column start time 3:15 pm 2nd column end time 3:30 pm or later "Biff" wrote: Hmmm.... After re-reading your post about 10 more times, I think I misunderstood the first time around. Why not just create separate lists. One that only contains the possible start times and another that only contains the possible end times. Biff "Biff" wrote in message ... Hi! So, there is a particular end time that corresponds to a particular start time? If so, why not just use a lookup formula? Biff "Steve" wrote in message ... I have set up a time sheet in which e6:e14 has a drop down list of times in 15 minute increments. This is the start time. I have g6:g14 with the same setup only this would be end time. Is there a way that when I enter the start time I will be able to enter the end time from the drop down list without having to scroll down from the begining? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet drop down lists
You don't enter the formula in a cell!
You use the formula as the source for the drop down. If your end time drop down is in cell B2: Select cell B2 Goto DataValidation Allow: List Source: enter the formula in this box OK If you can't get it to work let me know and I'll put together a sample that demonstrates this and post a link. NB: Depending on how you created your incremented list of times, there could be problems with matches. It's hard to explain but autofilling some types of series values can lead to very minute rounding differences. Biff "Steve" wrote in message ... We are getting close. This formula works great unless there is a drop down list, which I still need. When this formula is entered into a cell with a drop down list I get a #value error. Any suggestions? "Biff" wrote: OK, try this: Assume you have a list of times in 15m increments in the range G1:G20. This list is the source for your drop downs in A2 (start time) and B2 (end time). As the source for the drop down in B2 use this: =OFFSET(G1,MATCH(A2,G1:G20,0),,COUNT(G1:G20)-MATCH(A2,G1:G20,0)) So, if you select 9:00 AM as the start time from the drop down in cell A2 the drop down for cell B2 will start at 9:15 AM. Biff "Biff" wrote in message ... I would like to be lazy and not half to scroll from the top I understood that much! Let me play around with an idea I have. Biff "Steve" wrote in message ... Hey Biff, So much for being clear. Let me try again. I need to enter a start time in the first column. Then when I enter the end time I would like to be lazy and not half to scroll from the top of my time list shown in the drop down to a time after the start time. Example: 1st column start time 3:15 pm 2nd column end time 3:30 pm or later "Biff" wrote: Hmmm.... After re-reading your post about 10 more times, I think I misunderstood the first time around. Why not just create separate lists. One that only contains the possible start times and another that only contains the possible end times. Biff "Biff" wrote in message ... Hi! So, there is a particular end time that corresponds to a particular start time? If so, why not just use a lookup formula? Biff "Steve" wrote in message ... I have set up a time sheet in which e6:e14 has a drop down list of times in 15 minute increments. This is the start time. I have g6:g14 with the same setup only this would be end time. Is there a way that when I enter the start time I will be able to enter the end time from the drop down list without having to scroll down from the begining? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet drop down lists
Hey Biff,
That worked great. I have one question though. Is there a way to get the next start time (if there is one) to be based on the previous end time? "Biff" wrote: You don't enter the formula in a cell! You use the formula as the source for the drop down. If your end time drop down is in cell B2: Select cell B2 Goto DataValidation Allow: List Source: enter the formula in this box OK If you can't get it to work let me know and I'll put together a sample that demonstrates this and post a link. NB: Depending on how you created your incremented list of times, there could be problems with matches. It's hard to explain but autofilling some types of series values can lead to very minute rounding differences. Biff "Steve" wrote in message ... We are getting close. This formula works great unless there is a drop down list, which I still need. When this formula is entered into a cell with a drop down list I get a #value error. Any suggestions? "Biff" wrote: OK, try this: Assume you have a list of times in 15m increments in the range G1:G20. This list is the source for your drop downs in A2 (start time) and B2 (end time). As the source for the drop down in B2 use this: =OFFSET(G1,MATCH(A2,G1:G20,0),,COUNT(G1:G20)-MATCH(A2,G1:G20,0)) So, if you select 9:00 AM as the start time from the drop down in cell A2 the drop down for cell B2 will start at 9:15 AM. Biff "Biff" wrote in message ... I would like to be lazy and not half to scroll from the top I understood that much! Let me play around with an idea I have. Biff "Steve" wrote in message ... Hey Biff, So much for being clear. Let me try again. I need to enter a start time in the first column. Then when I enter the end time I would like to be lazy and not half to scroll from the top of my time list shown in the drop down to a time after the start time. Example: 1st column start time 3:15 pm 2nd column end time 3:30 pm or later "Biff" wrote: Hmmm.... After re-reading your post about 10 more times, I think I misunderstood the first time around. Why not just create separate lists. One that only contains the possible start times and another that only contains the possible end times. Biff "Biff" wrote in message ... Hi! So, there is a particular end time that corresponds to a particular start time? If so, why not just use a lookup formula? Biff "Steve" wrote in message ... I have set up a time sheet in which e6:e14 has a drop down list of times in 15 minute increments. This is the start time. I have g6:g14 with the same setup only this would be end time. Is there a way that when I enter the start time I will be able to enter the end time from the drop down list without having to scroll down from the begining? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet drop down lists
Are you talking about a 3rd and/or 4th drop down or are you talking about
looping back to the original start time drop down? Biff "Steve" wrote in message ... Hey Biff, That worked great. I have one question though. Is there a way to get the next start time (if there is one) to be based on the previous end time? "Biff" wrote: You don't enter the formula in a cell! You use the formula as the source for the drop down. If your end time drop down is in cell B2: Select cell B2 Goto DataValidation Allow: List Source: enter the formula in this box OK If you can't get it to work let me know and I'll put together a sample that demonstrates this and post a link. NB: Depending on how you created your incremented list of times, there could be problems with matches. It's hard to explain but autofilling some types of series values can lead to very minute rounding differences. Biff "Steve" wrote in message ... We are getting close. This formula works great unless there is a drop down list, which I still need. When this formula is entered into a cell with a drop down list I get a #value error. Any suggestions? "Biff" wrote: OK, try this: Assume you have a list of times in 15m increments in the range G1:G20. This list is the source for your drop downs in A2 (start time) and B2 (end time). As the source for the drop down in B2 use this: =OFFSET(G1,MATCH(A2,G1:G20,0),,COUNT(G1:G20)-MATCH(A2,G1:G20,0)) So, if you select 9:00 AM as the start time from the drop down in cell A2 the drop down for cell B2 will start at 9:15 AM. Biff "Biff" wrote in message ... I would like to be lazy and not half to scroll from the top I understood that much! Let me play around with an idea I have. Biff "Steve" wrote in message ... Hey Biff, So much for being clear. Let me try again. I need to enter a start time in the first column. Then when I enter the end time I would like to be lazy and not half to scroll from the top of my time list shown in the drop down to a time after the start time. Example: 1st column start time 3:15 pm 2nd column end time 3:30 pm or later "Biff" wrote: Hmmm.... After re-reading your post about 10 more times, I think I misunderstood the first time around. Why not just create separate lists. One that only contains the possible start times and another that only contains the possible end times. Biff "Biff" wrote in message ... Hi! So, there is a particular end time that corresponds to a particular start time? If so, why not just use a lookup formula? Biff "Steve" wrote in message ... I have set up a time sheet in which e6:e14 has a drop down list of times in 15 minute increments. This is the start time. I have g6:g14 with the same setup only this would be end time. Is there a way that when I enter the start time I will be able to enter the end time from the drop down list without having to scroll down from the begining? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet drop down lists
I have two columns one is start and the other is end times. Both columns
have drop down list of time in 15 minute increments. Each new row will indicate a new job's start and end time. I would like for each new job (if there is one) to draw off the previous end time. "Biff" wrote: Are you talking about a 3rd and/or 4th drop down or are you talking about looping back to the original start time drop down? Biff "Steve" wrote in message ... Hey Biff, That worked great. I have one question though. Is there a way to get the next start time (if there is one) to be based on the previous end time? "Biff" wrote: You don't enter the formula in a cell! You use the formula as the source for the drop down. If your end time drop down is in cell B2: Select cell B2 Goto DataValidation Allow: List Source: enter the formula in this box OK If you can't get it to work let me know and I'll put together a sample that demonstrates this and post a link. NB: Depending on how you created your incremented list of times, there could be problems with matches. It's hard to explain but autofilling some types of series values can lead to very minute rounding differences. Biff "Steve" wrote in message ... We are getting close. This formula works great unless there is a drop down list, which I still need. When this formula is entered into a cell with a drop down list I get a #value error. Any suggestions? "Biff" wrote: OK, try this: Assume you have a list of times in 15m increments in the range G1:G20. This list is the source for your drop downs in A2 (start time) and B2 (end time). As the source for the drop down in B2 use this: =OFFSET(G1,MATCH(A2,G1:G20,0),,COUNT(G1:G20)-MATCH(A2,G1:G20,0)) So, if you select 9:00 AM as the start time from the drop down in cell A2 the drop down for cell B2 will start at 9:15 AM. Biff "Biff" wrote in message ... I would like to be lazy and not half to scroll from the top I understood that much! Let me play around with an idea I have. Biff "Steve" wrote in message ... Hey Biff, So much for being clear. Let me try again. I need to enter a start time in the first column. Then when I enter the end time I would like to be lazy and not half to scroll from the top of my time list shown in the drop down to a time after the start time. Example: 1st column start time 3:15 pm 2nd column end time 3:30 pm or later "Biff" wrote: Hmmm.... After re-reading your post about 10 more times, I think I misunderstood the first time around. Why not just create separate lists. One that only contains the possible start times and another that only contains the possible end times. Biff "Biff" wrote in message ... Hi! So, there is a particular end time that corresponds to a particular start time? If so, why not just use a lookup formula? Biff "Steve" wrote in message ... I have set up a time sheet in which e6:e14 has a drop down list of times in 15 minute increments. This is the start time. I have g6:g14 with the same setup only this would be end time. Is there a way that when I enter the start time I will be able to enter the end time from the drop down list without having to scroll down from the begining? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet drop down lists
Hey biff,
I figured a way to do this. I entered the formula in the next start time cell =IF(A70,G6,). A7 is the next job name and G6 is the previous end time. It works well but is there a way to accomplish this same thing in the background so that the formulas will not be erased with a clear contents command? Thanks again. "Steve" wrote: I have two columns one is start and the other is end times. Both columns have drop down list of time in 15 minute increments. Each new row will indicate a new job's start and end time. I would like for each new job (if there is one) to draw off the previous end time. "Biff" wrote: Are you talking about a 3rd and/or 4th drop down or are you talking about looping back to the original start time drop down? Biff "Steve" wrote in message ... Hey Biff, That worked great. I have one question though. Is there a way to get the next start time (if there is one) to be based on the previous end time? "Biff" wrote: You don't enter the formula in a cell! You use the formula as the source for the drop down. If your end time drop down is in cell B2: Select cell B2 Goto DataValidation Allow: List Source: enter the formula in this box OK If you can't get it to work let me know and I'll put together a sample that demonstrates this and post a link. NB: Depending on how you created your incremented list of times, there could be problems with matches. It's hard to explain but autofilling some types of series values can lead to very minute rounding differences. Biff "Steve" wrote in message ... We are getting close. This formula works great unless there is a drop down list, which I still need. When this formula is entered into a cell with a drop down list I get a #value error. Any suggestions? "Biff" wrote: OK, try this: Assume you have a list of times in 15m increments in the range G1:G20. This list is the source for your drop downs in A2 (start time) and B2 (end time). As the source for the drop down in B2 use this: =OFFSET(G1,MATCH(A2,G1:G20,0),,COUNT(G1:G20)-MATCH(A2,G1:G20,0)) So, if you select 9:00 AM as the start time from the drop down in cell A2 the drop down for cell B2 will start at 9:15 AM. Biff "Biff" wrote in message ... I would like to be lazy and not half to scroll from the top I understood that much! Let me play around with an idea I have. Biff "Steve" wrote in message ... Hey Biff, So much for being clear. Let me try again. I need to enter a start time in the first column. Then when I enter the end time I would like to be lazy and not half to scroll from the top of my time list shown in the drop down to a time after the start time. Example: 1st column start time 3:15 pm 2nd column end time 3:30 pm or later "Biff" wrote: Hmmm.... After re-reading your post about 10 more times, I think I misunderstood the first time around. Why not just create separate lists. One that only contains the possible start times and another that only contains the possible end times. Biff "Biff" wrote in message ... Hi! So, there is a particular end time that corresponds to a particular start time? If so, why not just use a lookup formula? Biff "Steve" wrote in message ... I have set up a time sheet in which e6:e14 has a drop down list of times in 15 minute increments. This is the start time. I have g6:g14 with the same setup only this would be end time. Is there a way that when I enter the start time I will be able to enter the end time from the drop down list without having to scroll down from the begining? |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet drop down lists
Ok, but at some point you may "run out" of selections!
Just use the same basic setup: As the source for the next start time DD that's based on the last end time dd: =OFFSET(G1,MATCH(B2,G1:G20,0),,COUNT(G1:G20)-MATCH(B2,G1:G20,0)) Where B2 = the last end time drop down Basically, each one will cascade off of the previous one. But, like I said, you'll eventually run out (if you have enough of these)! Biff "Steve" wrote in message ... I have two columns one is start and the other is end times. Both columns have drop down list of time in 15 minute increments. Each new row will indicate a new job's start and end time. I would like for each new job (if there is one) to draw off the previous end time. "Biff" wrote: Are you talking about a 3rd and/or 4th drop down or are you talking about looping back to the original start time drop down? Biff "Steve" wrote in message ... Hey Biff, That worked great. I have one question though. Is there a way to get the next start time (if there is one) to be based on the previous end time? "Biff" wrote: You don't enter the formula in a cell! You use the formula as the source for the drop down. If your end time drop down is in cell B2: Select cell B2 Goto DataValidation Allow: List Source: enter the formula in this box OK If you can't get it to work let me know and I'll put together a sample that demonstrates this and post a link. NB: Depending on how you created your incremented list of times, there could be problems with matches. It's hard to explain but autofilling some types of series values can lead to very minute rounding differences. Biff "Steve" wrote in message ... We are getting close. This formula works great unless there is a drop down list, which I still need. When this formula is entered into a cell with a drop down list I get a #value error. Any suggestions? "Biff" wrote: OK, try this: Assume you have a list of times in 15m increments in the range G1:G20. This list is the source for your drop downs in A2 (start time) and B2 (end time). As the source for the drop down in B2 use this: =OFFSET(G1,MATCH(A2,G1:G20,0),,COUNT(G1:G20)-MATCH(A2,G1:G20,0)) So, if you select 9:00 AM as the start time from the drop down in cell A2 the drop down for cell B2 will start at 9:15 AM. Biff "Biff" wrote in message ... I would like to be lazy and not half to scroll from the top I understood that much! Let me play around with an idea I have. Biff "Steve" wrote in message ... Hey Biff, So much for being clear. Let me try again. I need to enter a start time in the first column. Then when I enter the end time I would like to be lazy and not half to scroll from the top of my time list shown in the drop down to a time after the start time. Example: 1st column start time 3:15 pm 2nd column end time 3:30 pm or later "Biff" wrote: Hmmm.... After re-reading your post about 10 more times, I think I misunderstood the first time around. Why not just create separate lists. One that only contains the possible start times and another that only contains the possible end times. Biff "Biff" wrote in message ... Hi! So, there is a particular end time that corresponds to a particular start time? If so, why not just use a lookup formula? Biff "Steve" wrote in message ... I have set up a time sheet in which e6:e14 has a drop down list of times in 15 minute increments. This is the start time. I have g6:g14 with the same setup only this would be end time. Is there a way that when I enter the start time I will be able to enter the end time from the drop down list without having to scroll down from the begining? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
drop down lists | New Users to Excel | |||
Excel Used as a Time Sheet | Excel Discussion (Misc queries) | |||
CREATING MULTIPLE DROP DOWN LISTS IN SAME EXCELWORKBOOK | Excel Discussion (Misc queries) | |||
Drop Down Lists | Excel Worksheet Functions | |||
Time Sheet | Excel Discussion (Misc queries) |