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" <[email protected]> wrote in message
news:[email protected]...
> 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.