Populate value in a particular cell

[fayez_MrExcel]

I create vba form accepting the annual leave of our employees and save it sheet1 which i named "Annual Leave Master File" in this sheet it contains the EMPLOYEE NUMBER, FROM & TO date, TYPE OF LEAVE which i represent by 2 letters only e.g. Sick Leave = "SC", Annual Leave "AL" etc. Now in Sheet2 i make a 1 year Calendar format like i.e. is in B6 until B17 i type Jan til Dec and in C5 until AG5 i type 1 to 31. Now my proble is i want to to populate the TYPE OF LEAVE of a particular employee, which is in sheet1, into sheet2 whenever i enter the employee number in C2 but of course it will also filter as per the specified year in D2. Example the legend "AL" will be placed in D6 to I6 (i.e. Jan 2 - 7) as the Annual Leave of an employee...Can anyone help me...?? thanks for the help..

 

[fairwinds]

Hi,

Try:

=IF(SUM(($A$2:$A$6=$G$1)*($B$2:$B$6<=DATE($J$1,$F4,G$2))*($C$2:$C$6>=DATE($J$1,$F4,G$2))),INDEX($D$1:$D$6,MAX(($A$2:$A$6=$G$1)*($B$2:$B$6<=DATE($J$1,$F4,G$2))*($C$2:$C$6>=DATE($J$1,$F4,G$2))*ROW($D$2:$D$6))),"")

Confirmed with Ctrl + shift + enter in G4 then dragged right / down.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Employee	start	end	type		Employee	1001		Year	2005
2	1001	2005-01-02	2005-01-04	SC		Day	1	2	3	4	5	6	7
3	1001	2005-02-01	2005-02-02	AL		Month
4	1001	2005-02-04	2005-02-10	SC		1		SC	SC	SC
5	1002	2005-02-02	2005-02-10	SC		2	AL	AL		SC	SC	SC	SC
6	1001	2005-04-05	2005-04-20	AL		3
7						4					AL	AL	AL
8						5
9						6
Sheet3

 

[fayez_MrExcel]

Only genius has the answer....thanks a lot...you solve my prob.....now i'll just try to separtate the data in other sheet and use your formula ...thanks......

 

[fayez_MrExcel]

hi,
just one query...i tried to transfer the data in the other sheet (sheet1) and the indexing in Sheet2. Eventually, i changed the $a$2:$a$6 to Sheet1!a2:a6..and so on....my question is why it doesnt show the value like it is on the same sheet?..thanks again

 

[fairwinds]

I gue ss you must ge some ref wrong. I fit is exactly the same locations but in two different shetts, formula should be:

=IF(SUM((Sheet1!$A$2:$A$6=$G$1)*(Sheet1!$B$2:$B$6<=DATE($J$1,$F4,G$2))*(Sheet1!$C$2:$C$6>=DATE($J$1,$F4,G$2))),INDEX(Sheet1!$D$1:$D$6,MAX((Sheet1!$A$2:$A$6=$G$1)*(Sheet1!$B$2:$B$6<=DATE($J$1,$F4,G$2))*(Sheet1!$C$2:$C$6>=DATE($J$1,$F4,G$2))*ROW(Sheet1!$D$2:$D$6))),"")

 

[fayez_MrExcel]

thanks...i got it already...However, does it really takes long to filter, well i have 500 employees...how can I make fast

 

[fairwinds]

Yes the formula is "expensive" and applied to so many cells...

Do you need to calcualate cell more than once? As you are using VBA, maybe you can calculate cell by cell only once or have the formula replaced by values once it is calculated.