Formula required

  • Thread starter Thread starter Skinman
  • Start date Start date
S

Skinman

Hi all, using excel 2007

I would like a formula to test a column to find out whether
the cells in the column are either a constant, a formula, or
a name that has been defined.

In A1 Putting the formula and filling down as many as required.
Something like:
=IF( B1 is a constant, "constant",IF( B1 is a name,"name", "formula"))

Is that possible in a formula or will the problem only be solved
with a macro?

Regards Skinman.
 
Try this small UDF:

Public Function typeeOf(r As Range) As String
Dim f As String, n As Name
Application.Volatile
f = r.Formula
If Left(f, 1) <> "=" Then
typeeOf = "constant"
Exit Function
End If
f = Right(f, Len(f) - 1)
For Each n In ActiveWorkbook.Names
If n.Name = f Then
typeeOf = "name"
Exit Function
End If
Next
typeeOf = "formula"
End Function
 
Back
Top