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.

Capture.JPG

Capture1.JPG

[BIFanatic]

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