VLOOKUP with WORKDAY?
 

Is there a way to lookup a range of holidays based on a person's name, and
then use that range in the WORKDAY function? I have a list of employees and
their days off (holidays) and I'd like to use a different range of holidays
for each employee. Ideas?
--
XLNut

VLOOKUP with WORKDAY?
 

Hi XLNut,

Perhaps following can bring you to some ideas to work it out further.

In the columns K:Z, I have in K the employees and in de columns beside their
days off.

This looks like:
K L M N O
P Q ...
JP 02/11/2009 03/11/2009 04/11/2009 05/11/2009
LO 05/11/2009 09/11/2009
PN 06/11/2009 12/11/2009 13/11/2009


In comlumn A, I have also the employees:
A B
JP
LO
PN


In B, I calculate their networkdays:
=NETWORKDAYS("1/11/2009";"30/11/2009";INDIRECT(ADDRESS(MATCH(A1;K:K;0);13;1)
& ":" & ADDRESS(MATCH(A1;K:K;0);26;1)))

Start date = November 1st
End date = last day of November

MATCH(A1;K:K;0);13;1) gives the row number of the employee in K:Z (K=column
11, Z = 26)
ADDRESS(MATCH(A1;K:K;0);12;1) = $L$1
ADDRESS(MATCH(A1;K:K;0);26;1)= $Z$1
Indirect to pick up the full range.

In this example, the functions returns:
JP 17
LO 19
PN 18

I'm sure there are better ways but it is already a starting point.

Wkr,

JP



"XLNut" wrote in message
...
Is there a way to lookup a range of holidays based on a person's name, and
then use that range in the WORKDAY function? I have a list of employees
and
their days off (holidays) and I'd like to use a different range of
holidays
for each employee. Ideas?
--
XLNut

VLOOKUP with WORKDAY?
 

On Nov 1, 12:12*pm, "JP Ronse" wrote:
Hi XLNut,

Perhaps following can bring you *to some ideas to work it out further.

In the columns K:Z, I have in K the employees and in de columns beside their
days off.

This looks like:
K * * * * * * * *L * * * * * * * *M * * * * * * * *N * * * * * * * O
P * * * * * *Q ...
* * * JP 02/11/2009 03/11/2009 04/11/2009 05/11/2009
* * * LO 05/11/2009 09/11/2009
* * * PN 06/11/2009 12/11/2009 13/11/2009

In comlumn A, I have also the employees:
A * * * *B
* * * JP
* * * LO
* * * PN

In B, I calculate their networkdays:
=NETWORKDAYS("1/11/2009";"30/11/2009";INDIRECT(ADDRESS(MATCH(A1;K:K;0);13;1*)
& ":" & ADDRESS(MATCH(A1;K:K;0);26;1)))

Start date = November 1st
End date = last day of November

MATCH(A1;K:K;0);13;1) gives the row number of the employee in K:Z (K=column
11, Z = 26)
ADDRESS(MATCH(A1;K:K;0);12;1) *= $L$1
ADDRESS(MATCH(A1;K:K;0);26;1)= $Z$1
Indirect to pick up the full range.

In this example, the functions returns:
JP * * * * *17
LO * * * * 19
PN * * * * 18

I'm sure there are better ways but it is already a starting point.

Wkr,

JP

"XLNut" wrote in message

...



Is there a way to lookup a range of holidays based on a person's name, and
then use that range in the WORKDAY function? I have a list of employees
and
their days off (holidays) and I'd like to use a different range of
holidays
for each employee. Ideas?
--
XLNut- Hide quoted text -

- Show quoted text -

Let's say you have employee names in row 1, e.g. H1:Z1.....and then
underneath you have the holidays listed for each down to row 20 then
you can use WORKDAY like this to add 10 days to a date in A2....for
employee shown in B2

=WORKDAY(A2,10,INDEX(H$2:Z$20,0,MATCH(B2,H$1:Z$1,0 )))

regards, barry

VLOOKUP with WORKDAY?
 

Hi Barry,

I knew for sure someone would find a better approach.

Wkr,

JP

"barry houdini" wrote in message
...
On Nov 1, 12:12 pm, "JP Ronse" wrote:
Hi XLNut,

Perhaps following can bring you to some ideas to work it out further.

In the columns K:Z, I have in K the employees and in de columns beside
their
days off.

This looks like:
K L M N O
P Q ...
JP 02/11/2009 03/11/2009 04/11/2009 05/11/2009
LO 05/11/2009 09/11/2009
PN 06/11/2009 12/11/2009 13/11/2009

In comlumn A, I have also the employees:
A B
JP
LO
PN

In B, I calculate their networkdays:
=NETWORKDAYS("1/11/2009";"30/11/2009";INDIRECT(ADDRESS(MATCH(A1;K:K;0);13;1*)
& ":" & ADDRESS(MATCH(A1;K:K;0);26;1)))

Start date = November 1st
End date = last day of November

MATCH(A1;K:K;0);13;1) gives the row number of the employee in K:Z
(K=column
11, Z = 26)
ADDRESS(MATCH(A1;K:K;0);12;1) = $L$1
ADDRESS(MATCH(A1;K:K;0);26;1)= $Z$1
Indirect to pick up the full range.

In this example, the functions returns:
JP 17
LO 19
PN 18

I'm sure there are better ways but it is already a starting point.

Wkr,

JP

"XLNut" wrote in message

...



Is there a way to lookup a range of holidays based on a person's name,
and
then use that range in the WORKDAY function? I have a list of employees
and
their days off (holidays) and I'd like to use a different range of
holidays
for each employee. Ideas?
--
XLNut- Hide quoted text -

- Show quoted text -

Let's say you have employee names in row 1, e.g. H1:Z1.....and then
underneath you have the holidays listed for each down to row 20 then
you can use WORKDAY like this to add 10 days to a date in A2....for
employee shown in B2

=WORKDAY(A2,10,INDEX(H$2:Z$20,0,MATCH(B2,H$1:Z$1,0 )))

regards, barry

VLOOKUP with WORKDAY?
 

Say we have 4 employees:
Larry
More
Curley
Shep

We make a holiday table, in F1 thru I4, enter:

Larry Moe Curley Shep
1/13/2009 1/13/2009 1/13/2009
1/14/2009 1/14/2009
1/15/2009
Then we create some Defined Names:

Larry for: F2
Moe for: G2
Curley for: H2:H3
Shep for: I2 thru I4

The names can now be used as holiday tables. The following formulas:

=NETWORKDAYS("1/12/2009","1/16/2009")
=NETWORKDAYS("1/12/2009","1/16/2009",Larry)
=NETWORKDAYS("1/12/2009","1/16/2009",Moe)
=NETWORKDAYS("1/12/2009","1/16/2009",Curley)
=NETWORKDAYS("1/12/2009","1/16/2009",Shep)

will display:

5
5
4
3
2

You can even use the name as a variable. In A1 enter:
Curley
and then:
=NETWORKDAYS("1/12/2009","1/16/2009",INDIRECT(A1))
will also display 3
--
Gary''s Student - gsnu200908


"XLNut" wrote:

Is there a way to lookup a range of holidays based on a person's name, and
then use that range in the WORKDAY function? I have a list of employees and
their days off (holidays) and I'd like to use a different range of holidays
for each employee. Ideas?
--
XLNut