hi,

i am using an excel worksheet for an employee schedule for about 60 people.
i need to make a daily workload for each day of the week and print it. i
inserted 7 worksheets within my sched worksheet. for sunday which will be say
sheet 1, if i enter an employee for that day, i want the worksheet to pickup
the shift and dept for that employee. eg if i enter in a1 in workshhet 1
"bob" whose name is on a29 in the sched worksheet, i need a2 and a3 in sheet
1 to pickup bob's shift and dept which is e29 and e30. and the same for the
names i enter down the list. the names i enter may be in random order. eg bob
(a29), joe (a31), pat (a47), tim (a131), etc
any ideas? thanks in advance

If you can re-arrange your schedule so that the information for each employee
is on one line (ie move dept to column F), you could use a Lookup.


Assuming your schedule is on a worksheet called Schedule, cells A1:F200:
=VLookup(A1, Schedule!$A$1:$F$200, 5, 0)
=VLookup(A1, Schedule!$A$1:$F$200, 6, 0)

Change ranges as needed, then copy down. The lookup will return the first
instance of the name you are looking up, so you'll need to make sure the
names are unique, or use something else as a "key", such as an employee
number.




"cj" wrote:

hi,

i am using an excel worksheet for an employee schedule for about 60 people.
i need to make a daily workload for each day of the week and print it. i
inserted 7 worksheets within my sched worksheet. for sunday which will be say
sheet 1, if i enter an employee for that day, i want the worksheet to pickup
the shift and dept for that employee. eg if i enter in a1 in workshhet 1
"bob" whose name is on a29 in the sched worksheet, i need a2 and a3 in sheet
1 to pickup bob's shift and dept which is e29 and e30. and the same for the
names i enter down the list. the names i enter may be in random order. eg bob
(a29), joe (a31), pat (a47), tim (a131), etc
any ideas? thanks in advance

Found a link to Debra Dalgleish's site for details on VLookp:
http://www.contextures.com/xlFunctions02.html

"JMB" wrote:

If you can re-arrange your schedule so that the information for each employee
is on one line (ie move dept to column F), you could use a Lookup.


Assuming your schedule is on a worksheet called Schedule, cells A1:F200:
=VLookup(A1, Schedule!$A$1:$F$200, 5, 0)
=VLookup(A1, Schedule!$A$1:$F$200, 6, 0)

Change ranges as needed, then copy down. The lookup will return the first
instance of the name you are looking up, so you'll need to make sure the
names are unique, or use something else as a "key", such as an employee
number.




"cj" wrote:

hi,

i am using an excel worksheet for an employee schedule for about 60 people.
i need to make a daily workload for each day of the week and print it. i
inserted 7 worksheets within my sched worksheet. for sunday which will be say
sheet 1, if i enter an employee for that day, i want the worksheet to pickup
the shift and dept for that employee. eg if i enter in a1 in workshhet 1
"bob" whose name is on a29 in the sched worksheet, i need a2 and a3 in sheet
1 to pickup bob's shift and dept which is e29 and e30. and the same for the
names i enter down the list. the names i enter may be in random order. eg bob
(a29), joe (a31), pat (a47), tim (a131), etc
any ideas? thanks in advance

thanks for your reply jmb

i enter the vlookup, but an update values screen pop up

"JMB" wrote:

Found a link to Debra Dalgleish's site for details on VLookp:
http://www.contextures.com/xlFunctions02.html

"JMB" wrote:

If you can re-arrange your schedule so that the information for each employee
is on one line (ie move dept to column F), you could use a Lookup.


Assuming your schedule is on a worksheet called Schedule, cells A1:F200:
=VLookup(A1, Schedule!$A$1:$F$200, 5, 0)
=VLookup(A1, Schedule!$A$1:$F$200, 6, 0)

Change ranges as needed, then copy down. The lookup will return the first
instance of the name you are looking up, so you'll need to make sure the
names are unique, or use something else as a "key", such as an employee
number.




"cj" wrote:

hi,

i am using an excel worksheet for an employee schedule for about 60 people.
i need to make a daily workload for each day of the week and print it. i
inserted 7 worksheets within my sched worksheet. for sunday which will be say
sheet 1, if i enter an employee for that day, i want the worksheet to pickup
the shift and dept for that employee. eg if i enter in a1 in workshhet 1
"bob" whose name is on a29 in the sched worksheet, i need a2 and a3 in sheet
1 to pickup bob's shift and dept which is e29 and e30. and the same for the
names i enter down the list. the names i enter may be in random order. eg bob
(a29), joe (a31), pat (a47), tim (a131), etc
any ideas? thanks in advance

Not sure I'm familiar with that pop up. can you post your formula?


"cj" wrote:

thanks for your reply jmb

i enter the vlookup, but an update values screen pop up

"JMB" wrote:

Found a link to Debra Dalgleish's site for details on VLookp:
http://www.contextures.com/xlFunctions02.html

"JMB" wrote:

If you can re-arrange your schedule so that the information for each employee
is on one line (ie move dept to column F), you could use a Lookup.


Assuming your schedule is on a worksheet called Schedule, cells A1:F200:
=VLookup(A1, Schedule!$A$1:$F$200, 5, 0)
=VLookup(A1, Schedule!$A$1:$F$200, 6, 0)

Change ranges as needed, then copy down. The lookup will return the first
instance of the name you are looking up, so you'll need to make sure the
names are unique, or use something else as a "key", such as an employee
number.




"cj" wrote:

hi,

i am using an excel worksheet for an employee schedule for about 60 people.
i need to make a daily workload for each day of the week and print it. i
inserted 7 worksheets within my sched worksheet. for sunday which will be say
sheet 1, if i enter an employee for that day, i want the worksheet to pickup
the shift and dept for that employee. eg if i enter in a1 in workshhet 1
"bob" whose name is on a29 in the sched worksheet, i need a2 and a3 in sheet
1 to pickup bob's shift and dept which is e29 and e30. and the same for the
names i enter down the list. the names i enter may be in random order. eg bob
(a29), joe (a31), pat (a47), tim (a131), etc
any ideas? thanks in advance

=vlookup(e1,chedule!$e$1:e$190,5,0)

"JMB" wrote:

Not sure I'm familiar with that pop up. can you post your formula?


"cj" wrote:

thanks for your reply jmb

i enter the vlookup, but an update values screen pop up

"JMB" wrote:

Found a link to Debra Dalgleish's site for details on VLookp:
http://www.contextures.com/xlFunctions02.html

"JMB" wrote:

If you can re-arrange your schedule so that the information for each employee
is on one line (ie move dept to column F), you could use a Lookup.


Assuming your schedule is on a worksheet called Schedule, cells A1:F200:
=VLookup(A1, Schedule!$A$1:$F$200, 5, 0)
=VLookup(A1, Schedule!$A$1:$F$200, 6, 0)

Change ranges as needed, then copy down. The lookup will return the first
instance of the name you are looking up, so you'll need to make sure the
names are unique, or use something else as a "key", such as an employee
number.




"cj" wrote:

hi,

i am using an excel worksheet for an employee schedule for about 60 people.
i need to make a daily workload for each day of the week and print it. i
inserted 7 worksheets within my sched worksheet. for sunday which will be say
sheet 1, if i enter an employee for that day, i want the worksheet to pickup
the shift and dept for that employee. eg if i enter in a1 in workshhet 1
"bob" whose name is on a29 in the sched worksheet, i need a2 and a3 in sheet
1 to pickup bob's shift and dept which is e29 and e30. and the same for the
names i enter down the list. the names i enter may be in random order. eg bob
(a29), joe (a31), pat (a47), tim (a131), etc
any ideas? thanks in advance

There are not 5 columns in schedule!$e$1:e$190.

The first column of this range s/b your "key" (ie - the unique values that
you are going to match to). Since you want to return something that is 4
columns over, this table has to be at least 5 columns wide.

So if your "key" is in column A and the value you want to return is in
column E, you would use

=vlookup(e1,schedule!$A$1:$E$190,5,0)

It will look E1 up in Schedule!A1:A190 and return the corresponding value in
Schedule!E1:E190.

Does that help?


"cj" wrote:

=vlookup(e1,schedule!$e$1:e$190,5,0)

"JMB" wrote:

Not sure I'm familiar with that pop up. can you post your formula?


"cj" wrote:

thanks for your reply jmb

i enter the vlookup, but an update values screen pop up

"JMB" wrote:

Found a link to Debra Dalgleish's site for details on VLookp:
http://www.contextures.com/xlFunctions02.html

"JMB" wrote:

If you can re-arrange your schedule so that the information for each employee
is on one line (ie move dept to column F), you could use a Lookup.


Assuming your schedule is on a worksheet called Schedule, cells A1:F200:
=VLookup(A1, Schedule!$A$1:$F$200, 5, 0)
=VLookup(A1, Schedule!$A$1:$F$200, 6, 0)

Change ranges as needed, then copy down. The lookup will return the first
instance of the name you are looking up, so you'll need to make sure the
names are unique, or use something else as a "key", such as an employee
number.




"cj" wrote:

hi,

i am using an excel worksheet for an employee schedule for about 60 people.
i need to make a daily workload for each day of the week and print it. i
inserted 7 worksheets within my sched worksheet. for sunday which will be say
sheet 1, if i enter an employee for that day, i want the worksheet to pickup
the shift and dept for that employee. eg if i enter in a1 in workshhet 1
"bob" whose name is on a29 in the sched worksheet, i need a2 and a3 in sheet
1 to pickup bob's shift and dept which is e29 and e30. and the same for the
names i enter down the list. the names i enter may be in random order. eg bob
(a29), joe (a31), pat (a47), tim (a131), etc
any ideas? thanks in advance

i tried that, still pop up a values update:schedule box
could it be because i'm trying to use the workload sheet in the same
workbook as the sched?
"JMB" wrote:

There are not 5 columns in schedule!$e$1:e$190.

The first column of this range s/b your "key" (ie - the unique values that
you are going to match to). Since you want to return something that is 4
columns over, this table has to be at least 5 columns wide.

So if your "key" is in column A and the value you want to return is in
column E, you would use

=vlookup(e1,schedule!$A$1:$E$190,5,0)

It will look E1 up in Schedule!A1:A190 and return the corresponding value in
Schedule!E1:E190.

Does that help?


"cj" wrote:

=vlookup(e1,schedule!$e$1:e$190,5,0)

"JMB" wrote:

Not sure I'm familiar with that pop up. can you post your formula?


"cj" wrote:

thanks for your reply jmb

i enter the vlookup, but an update values screen pop up

"JMB" wrote:

Found a link to Debra Dalgleish's site for details on VLookp:
http://www.contextures.com/xlFunctions02.html

"JMB" wrote:

If you can re-arrange your schedule so that the information for each employee
is on one line (ie move dept to column F), you could use a Lookup.


Assuming your schedule is on a worksheet called Schedule, cells A1:F200:
=VLookup(A1, Schedule!$A$1:$F$200, 5, 0)
=VLookup(A1, Schedule!$A$1:$F$200, 6, 0)

Change ranges as needed, then copy down. The lookup will return the first
instance of the name you are looking up, so you'll need to make sure the
names are unique, or use something else as a "key", such as an employee
number.




"cj" wrote:

hi,

i am using an excel worksheet for an employee schedule for about 60 people.
i need to make a daily workload for each day of the week and print it. i
inserted 7 worksheets within my sched worksheet. for sunday which will be say
sheet 1, if i enter an employee for that day, i want the worksheet to pickup
the shift and dept for that employee. eg if i enter in a1 in workshhet 1
"bob" whose name is on a29 in the sched worksheet, i need a2 and a3 in sheet
1 to pickup bob's shift and dept which is e29 and e30. and the same for the
names i enter down the list. the names i enter may be in random order. eg bob
(a29), joe (a31), pat (a47), tim (a131), etc
any ideas? thanks in advance

vlookup should work fine with the workload sheet and schedule sheet in the
same workbook.

what version of XL do you have? the only time I've seen messages pertaining
to updating values is when my workbook contains links to another workbook.


"cj" wrote:

i tried that, still pop up a values update:schedule box
could it be because i'm trying to use the workload sheet in the same
workbook as the sched?
"JMB" wrote:

There are not 5 columns in schedule!$e$1:e$190.

The first column of this range s/b your "key" (ie - the unique values that
you are going to match to). Since you want to return something that is 4
columns over, this table has to be at least 5 columns wide.

So if your "key" is in column A and the value you want to return is in
column E, you would use

=vlookup(e1,schedule!$A$1:$E$190,5,0)

It will look E1 up in Schedule!A1:A190 and return the corresponding value in
Schedule!E1:E190.

Does that help?


"cj" wrote:

=vlookup(e1,schedule!$e$1:e$190,5,0)

"JMB" wrote:

Not sure I'm familiar with that pop up. can you post your formula?


"cj" wrote:

thanks for your reply jmb

i enter the vlookup, but an update values screen pop up

"JMB" wrote:

Found a link to Debra Dalgleish's site for details on VLookp:
http://www.contextures.com/xlFunctions02.html

"JMB" wrote:

If you can re-arrange your schedule so that the information for each employee
is on one line (ie move dept to column F), you could use a Lookup.


Assuming your schedule is on a worksheet called Schedule, cells A1:F200:
=VLookup(A1, Schedule!$A$1:$F$200, 5, 0)
=VLookup(A1, Schedule!$A$1:$F$200, 6, 0)

Change ranges as needed, then copy down. The lookup will return the first
instance of the name you are looking up, so you'll need to make sure the
names are unique, or use something else as a "key", such as an employee
number.




"cj" wrote:

hi,

i am using an excel worksheet for an employee schedule for about 60 people.
i need to make a daily workload for each day of the week and print it. i
inserted 7 worksheets within my sched worksheet. for sunday which will be say
sheet 1, if i enter an employee for that day, i want the worksheet to pickup
the shift and dept for that employee. eg if i enter in a1 in workshhet 1
"bob" whose name is on a29 in the sched worksheet, i need a2 and a3 in sheet
1 to pickup bob's shift and dept which is e29 and e30. and the same for the
names i enter down the list. the names i enter may be in random order. eg bob
(a29), joe (a31), pat (a47), tim (a131), etc
any ideas? thanks in advance

i got microsoft excel xp pofessional


"JMB" wrote:

vlookup should work fine with the workload sheet and schedule sheet in the
same workbook.

what version of XL do you have? the only time I've seen messages pertaining
to updating values is when my workbook contains links to another workbook.


"cj" wrote:

i tried that, still pop up a values update:schedule box
could it be because i'm trying to use the workload sheet in the same
workbook as the sched?
"JMB" wrote:

There are not 5 columns in schedule!$e$1:e$190.

The first column of this range s/b your "key" (ie - the unique values that
you are going to match to). Since you want to return something that is 4
columns over, this table has to be at least 5 columns wide.

So if your "key" is in column A and the value you want to return is in
column E, you would use

=vlookup(e1,schedule!$A$1:$E$190,5,0)

It will look E1 up in Schedule!A1:A190 and return the corresponding value in
Schedule!E1:E190.

Does that help?


"cj" wrote:

=vlookup(e1,schedule!$e$1:e$190,5,0)

"JMB" wrote:

Not sure I'm familiar with that pop up. can you post your formula?


"cj" wrote:

thanks for your reply jmb

i enter the vlookup, but an update values screen pop up

"JMB" wrote:

Found a link to Debra Dalgleish's site for details on VLookp:
http://www.contextures.com/xlFunctions02.html

"JMB" wrote:

If you can re-arrange your schedule so that the information for each employee
is on one line (ie move dept to column F), you could use a Lookup.


Assuming your schedule is on a worksheet called Schedule, cells A1:F200:
=VLookup(A1, Schedule!$A$1:$F$200, 5, 0)
=VLookup(A1, Schedule!$A$1:$F$200, 6, 0)

Change ranges as needed, then copy down. The lookup will return the first
instance of the name you are looking up, so you'll need to make sure the
names are unique, or use something else as a "key", such as an employee
number.




"cj" wrote:

hi,

i am using an excel worksheet for an employee schedule for about 60 people.
i need to make a daily workload for each day of the week and print it. i
inserted 7 worksheets within my sched worksheet. for sunday which will be say
sheet 1, if i enter an employee for that day, i want the worksheet to pickup
the shift and dept for that employee. eg if i enter in a1 in workshhet 1
"bob" whose name is on a29 in the sched worksheet, i need a2 and a3 in sheet
1 to pickup bob's shift and dept which is e29 and e30. and the same for the
names i enter down the list. the names i enter may be in random order. eg bob
(a29), joe (a31), pat (a47), tim (a131), etc
any ideas? thanks in advance

Is that XL2002? Perhaps see if you can save a copy as XL 2000 and email it
to me (if you wish) and I'll see if there's anything else I can do.

jeffblakleyAThotmailDOTcom


"cj" wrote:

i got microsoft excel xp pofessional


"JMB" wrote:

vlookup should work fine with the workload sheet and schedule sheet in the
same workbook.

what version of XL do you have? the only time I've seen messages pertaining
to updating values is when my workbook contains links to another workbook.


"cj" wrote:

i tried that, still pop up a values update:schedule box
could it be because i'm trying to use the workload sheet in the same
workbook as the sched?
"JMB" wrote:

There are not 5 columns in schedule!$e$1:e$190.

The first column of this range s/b your "key" (ie - the unique values that
you are going to match to). Since you want to return something that is 4
columns over, this table has to be at least 5 columns wide.

So if your "key" is in column A and the value you want to return is in
column E, you would use

=vlookup(e1,schedule!$A$1:$E$190,5,0)

It will look E1 up in Schedule!A1:A190 and return the corresponding value in
Schedule!E1:E190.

Does that help?


"cj" wrote:

=vlookup(e1,schedule!$e$1:e$190,5,0)

"JMB" wrote:

Not sure I'm familiar with that pop up. can you post your formula?


"cj" wrote:

thanks for your reply jmb

i enter the vlookup, but an update values screen pop up

"JMB" wrote:

Found a link to Debra Dalgleish's site for details on VLookp:
http://www.contextures.com/xlFunctions02.html

"JMB" wrote:

If you can re-arrange your schedule so that the information for each employee
is on one line (ie move dept to column F), you could use a Lookup.


Assuming your schedule is on a worksheet called Schedule, cells A1:F200:
=VLookup(A1, Schedule!$A$1:$F$200, 5, 0)
=VLookup(A1, Schedule!$A$1:$F$200, 6, 0)

Change ranges as needed, then copy down. The lookup will return the first
instance of the name you are looking up, so you'll need to make sure the
names are unique, or use something else as a "key", such as an employee
number.




"cj" wrote:

hi,

i am using an excel worksheet for an employee schedule for about 60 people.
i need to make a daily workload for each day of the week and print it. i
inserted 7 worksheets within my sched worksheet. for sunday which will be say
sheet 1, if i enter an employee for that day, i want the worksheet to pickup
the shift and dept for that employee. eg if i enter in a1 in workshhet 1
"bob" whose name is on a29 in the sched worksheet, i need a2 and a3 in sheet
1 to pickup bob's shift and dept which is e29 and e30. and the same for the
names i enter down the list. the names i enter may be in random order. eg bob
(a29), joe (a31), pat (a47), tim (a131), etc
any ideas? thanks in advance