Tracking annual leave

  • Thread starter Thread starter opos
  • Start date Start date
O

opos

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
 
Done,

Try this formula

=IF(ISERROR(TRANSPOSE(SMALL(IF($B$2:$AH$2=A10,COLUMN(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)
 
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 said:
Done,

Try this formula

=IF(ISERROR(TRANSPOSE(SMALL(IF($B$2:$AH$2=A10,COLUMN(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)
Click to expand...
 
Thank you Bob! Tried this out as I think you suggested but dates ar
not being shown correctly.

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

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

Regards

Do
 
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)
 
Back
Top