Populate value in a particular cell

fayez_MrExcel

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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

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
408
Office Version
  1. 365
Platform
  1. Windows
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
408
Office Version
  1. 365
Platform
  1. Windows
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
408
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,120
Messages
5,835,504
Members
430,362
Latest member
ConnerJ

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
Top