VLOOKUP with WORKDAY?

[XLNut]

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?

 

[JP Ronse]

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

 

[barry houdini]

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






- 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

 

[JP Ronse]

Hi Barry,

I knew for sure someone would find a better approach.

Wkr,

JP

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






- 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

 

[Gary''s Student]

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