Tracking annual leave
 

Hi hope you can help. I have a table listing the attendance of workers.
Their attendance is logged using a series of codes, 1 for example
showing they turned up for work. There is another "PH" (public holiday
Leave which is inserted in their row if they take a PH leave day during
that month.

Each person has Ph Leave days allocatted and what I need to be able to
do is to be able to list down the dates someone takes a PH leave day in
their PH leave table, with each date listed below eachother.

Hope I have explained this ok and would appreciate all and any help.

don


--
opos
------------------------------------------------------------------------
opos's Profile: http://www.excelforum.com/member.php...o&userid=26260
View this thread: http://www.excelforum.com/showthread...hreadid=395557

Done,

Try this formula

=IF(ISERROR(TRANSPOSE(SMALL(IF($B$2:$AH$2=A10,COLU MN(B2:AH2)-1,""),COLUMN(B2
:AH2)-1))),"",INDEX($B$1:$AH$1,TRANSPOSE(SMALL(IF($B$2:$ AH$2=A10,COLUMN(B2:A
H2)-1,""),COLUMN(B2:AH2)-1))))

Put PH in A10, then select the maximum number of cells per leave, hit F2,
enter the formula. It is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"opos" wrote in message
...

Hi hope you can help. I have a table listing the attendance of workers.
Their attendance is logged using a series of codes, 1 for example
showing they turned up for work. There is another "PH" (public holiday
Leave which is inserted in their row if they take a PH leave day during
that month.

Each person has Ph Leave days allocatted and what I need to be able to
do is to be able to list down the dates someone takes a PH leave day in
their PH leave table, with each date listed below eachother.

Hope I have explained this ok and would appreciate all and any help.

don


--
opos
------------------------------------------------------------------------
opos's Profile:
http://www.excelforum.com/member.php...o&userid=26260
View this thread: http://www.excelforum.com/showthread...hreadid=395557

I forgot to mention that my assumptions were

- the codes are in B2:AH2
- the dates are in B1:AH1

adjust to suit.

If your data is vertical rather than horizontal, post back, the formula
needs a tweak.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
Done,

Try this formula


=IF(ISERROR(TRANSPOSE(SMALL(IF($B$2:$AH$2=A10,COLU MN(B2:AH2)-1,""),COLUMN(B2

:AH2)-1))),"",INDEX($B$1:$AH$1,TRANSPOSE(SMALL(IF($B$2:$ AH$2=A10,COLUMN(B2:A
H2)-1,""),COLUMN(B2:AH2)-1))))

Put PH in A10, then select the maximum number of cells per leave, hit F2,
enter the formula. It is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"opos" wrote in
message
...

Hi hope you can help. I have a table listing the attendance of workers.
Their attendance is logged using a series of codes, 1 for example
showing they turned up for work. There is another "PH" (public holiday
Leave which is inserted in their row if they take a PH leave day during
that month.

Each person has Ph Leave days allocatted and what I need to be able to
do is to be able to list down the dates someone takes a PH leave day in
their PH leave table, with each date listed below eachother.

Hope I have explained this ok and would appreciate all and any help.

don


--
opos
------------------------------------------------------------------------
opos's Profile:
http://www.excelforum.com/member.php...o&userid=26260
View this thread:
http://www.excelforum.com/showthread...hreadid=395557

Thank you Bob! Tried this out as I think you suggested but dates are
not being shown correctly.

I chose 2,3,6,10/7/05 as ph days but formula returns 2,4,8,13/7/05 any
ideas?

I did add an absolute to the A10 refrences! have I executed this
wrongly?

Regards

Don


--
opos
------------------------------------------------------------------------
opos's Profile: http://www.excelforum.com/member.php...o&userid=26260
View this thread: http://www.excelforum.com/showthread...hreadid=395557

Post with some sample data , cell references and cell data. Make sure it is
clear so we can see what is where as the newsgroups are always the clearest.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"opos" wrote in message
...

Thank you Bob! Tried this out as I think you suggested but dates are
not being shown correctly.

I chose 2,3,6,10/7/05 as ph days but formula returns 2,4,8,13/7/05 any
ideas?

I did add an absolute to the A10 refrences! have I executed this
wrongly?

Regards

Don


--
opos
------------------------------------------------------------------------
opos's Profile:
http://www.excelforum.com/member.php...o&userid=26260
View this thread: http://www.excelforum.com/showthread...hreadid=395557