# Populate value in a particular cell

#### fayez_MrExcel

##### Active Member
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

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......

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

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))),"")

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

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.

Replies
0
Views
197
Replies
0
Views
144
Replies
1
Views
518
Replies
1
Views
353
Replies
0
Views
672

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.

### Which adblocker are you using?

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

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