I'm trying to produce a macro in Excel that auto populates a cell depending on the value of another cell.
For example, if A1=John Smith, then I want B2=Man, as an example.
A1 is currently a dropdown list of about 20 names, so I can't do a long if statement in the formula bar. Macros are new to me so struggling a little bit!
At the moment, I've put 3 names into a macro like this -
In the cell, I'm putting "=role_name(A1)" but getting back a REF error. Can anyone help?!
For example, if A1=John Smith, then I want B2=Man, as an example.
A1 is currently a dropdown list of about 20 names, so I can't do a long if statement in the formula bar. Macros are new to me so struggling a little bit!
At the moment, I've put 3 names into a macro like this -
Code:
Function role_name(roleVar As String) As Long
If roleVar = "John" Then
role = C
ElseIf roleVar = "James" Then
role = CT
ElseIf roleVar = "Matthew" Then
role = C
ElseIf roleVar = "Victor" Then
role = SC
Else
role = NA
End If
End FunctionIn the cell, I'm putting "=role_name(A1)" but getting back a REF error. Can anyone help?!