A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

time sheet drop down lists



 
 
Thread Tools Display Modes
  #1  
Old March 16th, 2006, 10:45 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default 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  
Old March 16th, 2006, 11:10 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default 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  
Old March 17th, 2006, 12:01 AM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default 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  
Old March 17th, 2006, 12:26 AM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default 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  
Old March 17th, 2006, 01:05 AM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default 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  
Old March 17th, 2006, 01:24 AM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default 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  
Old March 17th, 2006, 08:40 AM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default 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  
Old March 17th, 2006, 09:26 AM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default 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  
Old March 18th, 2006, 06:09 AM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default 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  
Old March 18th, 2006, 07:40 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default 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?















 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Time Zone suggestions for Outlook Calendar Ben Knox Calendar 2 October 20th, 2005 03:42 PM
Excel formula for a time sheet HRMSN Worksheet Functions 1 August 10th, 2005 03:07 PM
Please Help! Ref Article ID 210504 Telobamipada New Users 8 May 9th, 2005 01:09 PM
Time Sheet Calcuations ELL Database Design 3 October 7th, 2004 02:26 PM
Use first record found in expression? CASJAS Running & Setting Up Queries 17 July 22nd, 2004 09:21 PM


All times are GMT +1. The time now is 07:10 AM.


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