Populate value in a particular cell

fayez_MrExcel

Active Member
Joined
Oct 29, 2005
Messages
389
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..
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
ABCDEFGHIJKLM
1EmployeestartendtypeEmployee1001Year2005
210012005-01-022005-01-04SCDay1234567
310012005-02-012005-02-02ALMonth
410012005-02-042005-02-10SC1 SCSCSC   
510022005-02-022005-02-10SC2ALAL SCSCSCSC
610012005-04-052005-04-20AL3       
74    ALALAL
85       
96       
Sheet3
 

fayez_MrExcel

Active Member
Joined
Oct 29, 2005
Messages
389
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

Active Member
Joined
Oct 29, 2005
Messages
389
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

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

Active Member
Joined
Oct 29, 2005
Messages
389
thanks...i got it already...However, does it really takes long to filter, well i have 500 employees...how can I make fast
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,037
Messages
5,569,791
Members
412,292
Latest member
The Bear named Joe
Top