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