Calendar to bring the Leave Type from Leave Data to Calendar

[sethu29]

Hi Support,


I've two Sheets. Sheet one (Employee Details) with Date as Header. I'm using this as calendar.... I would like to bring the Leave Type from Data Sheet to Employee Details Sheet.


1) Criteria 1- Only Approved Request (Status)
2) Criteria 2- Name match with Employee Name for the particular Date available in Employee Details Sheet
3)Criteria 3 - Within Start Date and End Date

Example Output below



Date
6/1/2020
6/2/2020
6/3/2020


Employee1
PL
CL
CL


Employee2
CL
CL
CL


Employee3
CL
CL
CL


Please help as this is very urgent

If no Leave , then leave it blank
Thank you
Sethu

[BIFanatic]

Try this, enter with CTRL + SHIFT + ENTER, if you have office 365 then just press enter



=TEXTJOIN(", ",TRUE, T(IF((B$1>=Data!$B$2:$B$10)*('Employee Details'!B$1<=Data!$C$2:$C$10)*($A12=Data!$A$2:$A$10),Data!$D$2:$D$10)))


Here is the VBA solution, I didn't have much fantasy today in naming my variables but it works.



Function FindAndConcatenate(FindVal As Range, FindRange As Range, FindDate As Date) As String

'FindVal = Select the employee name in the sheet 1
'FindRange = Select the lookup range in sheet 2
'FindDate = select the date in the row 1 on sheet 1

Dim v As Variant
Dim x As Long
Dim s As String


v = FindRange

For x = 1 To UBound(v, 1)
If v(x, 1) = FindVal.Value Then
If FindDate >= v(x, 2) And FindDate <= v(x, 3) Then
s = s & v(x, 4) & ", "
End If
End If
Next x

If s = "" Then
FindAndConcatenate = ""
Else
FindAndConcatenate = Left(s, Len(s) - 2)
End If

End Function

[sethu29]

Hi Support,

I tried to paste the VBA and Formula. It seems not working . Please check the attached Workbook, after i applied the formula.

26863

26864

[BIFanatic]

Which version of the office are you using? I have entered the VBA solution for you.