Populate value in a particular cell

fayez_MrExcel

Active Member
Joined
Oct 29, 2005
Messages
437
Office Version
  1. 365
Platform
  1. Windows
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..
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
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......
 
Upvote 0
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
 
Upvote 0
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))),"")
 
Upvote 0
thanks...i got it already...However, does it really takes long to filter, well i have 500 employees...how can I make fast
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,211,987
Messages
6,105,227
Members
447,957
Latest member
Basildon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top