Excel Custom Function

That works perfect. Thanks!

perfect!!!!

Had the same problem, found this answer and works an abolute treat. Saved a lot of work - thank you!!!

hi...thanks for the try...maybe if u have a look at the code u can get a better picture
all is want is the formula to refresh itslef when u open or close workbook.


Function dis_multiple(end_date As Variant, msr_type As String, scheme As String, category As String, dob As Variant) As Variant
Dim title_row As Long
Dim age As Long
'Dim end_date As Date

Application.Calculation = xlCalculationAutomatic


Row = ActiveCell.Row
title_row = 5
age = Year(Now()) - Year(dob)

With ActiveSheet
msr_type = .Cells(Row, GetCol(title_row, "MSR Type"))
scheme = .Cells(Row, GetCol(title_row, "Scheme"))
category = .Cells(Row, GetCol(title_row, "Leaver Category"))
dob = .Cells(Row, GetCol(title_row, "DOB"))
end_date = .Cells(Row, GetCol(title_row, "Last Day Of Paid Service / Employment End Date"))
End With


end_month = Month(end_date)

'If msr_type = "" Or scheme = "" Or dob > 1900 Then

If msr_type = "New Joiners" And dob > 1900 Then
If scheme = "SORAS/DIS" Then
If age < 58 Then
dis_multiple = 4
Else
dis_multiple = 1
End If

Else
End If

Else
'MsgBox "Please see that the MSR Type and Date of Birth fields are filled in for New Joiners"
'Exit Function
End If

If msr_type = "Salary Alterations" Or msr_type = "Multiple Change" Or msr_type = "Transfers" Then
If scheme = "SORAS/DIS" Then
dis_multiple = 4
Else
End If
Else
End If



If msr_type = "Leavers" And scheme = "SORAS/DIS" Then
If category = "Regular" Then '
If end_month = Month(Now()) Then
dis_multiple = 4
Else
dis_multiple = 1
End If
ElseIf category = "Absconder" Then
dis_multiple = 4
ElseIf category = "Voluntary" Then
dis_multiple = 1
Else
MsgBox "Leaver category is blank"
End If
Else
End If


If msr_type = "New Joiners" Or msr_type = "Salary Alterations" Or msr_type = "Multiple Change" Or msr_type = "Transfers" Then
If scheme = "DIS/MS" Then
dis_multiple = 1
Else
End If
Else
End If

If msr_type = "Leavers" And scheme = "DIS/MS" Then
dis_multiple = 1
Else
End If

If msr_type = "New Joiners" Or msr_type = "Salary Alterations" Or msr_type = "Multiple Change" Or msr_type = "Transfers" Then
If scheme = "MS" Then
dis_multiple = 1
Else
End If
Else
End If

If msr_type = "" And scheme = "" And category = "" And dob = "" And end_date = "" Then
dis_multiple = ""
Else
End If


If scheme = "MS" Then
dis_multiple = ""
Else
End If


Application.Calculation = xlCalculationAutomati
End Function