indicate whether a cell contains a formula
 

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

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)


"chopsx7" wrote in message
...
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

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



-----Original Message-----
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
.

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?

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 "".

-----Original Message-----
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 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.