indicate whether a cell contains a formula

[Guest]

can you use an IF function (or any function) to return a value if a certain
cell contains a formula as opposed to an inputted value. I would like to be
able to see this indicator on a printout out of the spreadsheet. Thanks

 

[Bob Phillips]

Create a UDF and use that in the IF test

Function IsFormula(rng As Range)
IsFormula = rng.HasFormula
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jason Morin]

You can't do it directly with a worksheet formula, but a
simple UDF would work. Something like:

Function IsFormula(Cell As Range) As String
If Cell.HasFormula = True Then
IsFormula = "Formula"
Else
IsFormula = ""
End If
End Function

---
To use, press ALT+F11, go to Insert > Module, and paste
in the code. Press ALT+Q. Call the UDF in a cell as:

=isformula(A1)

Of course the formula will display "Formula" in cases
like "=100".

HTH
Jason
Atlanta, GA

 

[Harlan Grove]

Jason Morin wrote...

You can't do it directly with a worksheet formula, but a
simple UDF would work. Something like:

Function IsFormula(Cell As Range) As String
If Cell.HasFormula = True Then
IsFormula = "Formula"
Else
IsFormula = ""
End If
End Function

....

Obtuse! The OP wants to do something other than show Formula or not.
Didn't occur to you just to return the value of the range's .HasFormula
property?

 

[Jason Morin]

Yes, simply returning a TRUE or FALSE occurred to me. But
if you're sharing the worksheet with others, such as a
boss, it's more time-consuming to explain TRUE or FALSE
then "Formula" and "".

 

[Harlan Grove]

Jason Morin wrote...

Yes, simply returning a TRUE or FALSE occurred to me. But
if you're sharing the worksheet with others, such as a
boss, it's more time-consuming to explain TRUE or FALSE
then "Formula" and "".

....

The prototypical pointy-haired boss who doesn't understand the
explanations? Now, since you named the formula IsFormula, and since
there are several built-in functions (in English language versions)
beginning with IS that return True/False, just how pig-headed do you
believe these co-workers are?

Silly me believing that semantic consistency with similarly named,
similarly used built-in functions would cause less trouble in the long
run.