PROBLEM WITH FORMULA / FUNCTION

[team.sitel]

Hi... Any one helping me with this.

I have an employee attendance sheet wherein I have Employee Names in COLUMNs & Dates in ROWs. What i want is if suppose I have taken a LEAVE on 08th Jan 2005 (code for LEAVE = "L") it should give me a list of LEAVE's that i have taken in the year 2005 with dates in ROWs.
For EX....
Date: 08 Jan 13 Mar
L L

ANY SUGGESTIONS......

 

[fairwinds]

Welcome to the board!

Try:

=SMALL(IF(INDEX($B$2:$G$10,MATCH($A14,$A$2:$A$10,0),0)="L",$B$1:$G$1),COLUMN()-COLUMN($A$14))

Confirmed with Ctrl + Shift + Enter in B14, then dragged right.

Book1
	A	B	C	D	E	F	G
1		2002-10-10	2002-10-11	2002-10-12	2002-10-13	2002-10-14	2002-10-15
2	Name 1
3	Name 2				L
4	Name 3		L
5	Name 4			L	L		L
6	Name 5				L
7	Name 6		L
8	Name 7
9	Name 8				L
10	Name 9
11
12
13
14	Name 4	2002-10-12	2002-10-13	2002-10-15
15
Sheet7