Does cell contain a formula?

  • Thread starter Thread starter mlomax
  • Start date Start date
M

mlomax

I am looking for a way to tell if a cell contains a formula or not.

I am writing a formula in one cell (say D10) that looks at another cel
(say E10) and determines if the E10 contains a formula. If E10 doe
contain a formula, it would mean that the value in E10 is a projecte
value and I would then place the word "Projected" into D10 indicatin
the type of value located in E10. If, on the other hand, the user ha
typed a value into E10 overwriting the formula that existed ther
before, then I would set the value in D10 to "Actual".

Not only is this problem hard to research in Excel help but on thi
site as well. It is hard to determine what search words to use to fin
the problem. Therefore this may be documented somewhere else on thi
site but I have been unable to find it.

Any help would be greatly appreciated.....


THANKS IN ADVANC
 
Hi
you may try the following UDF (put in a module of your workbook)

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

Now enter the following in D10:
=IF(is_formula(E10),"Projected","Actual")
 
Thanks Frank

I was hoping for something I could just put in a function since I a
not an advanced Excel user. However I can try your suggestion if yo
give me some more direction.

Not sure what you mean by "UDF (put a module in your workbook)".
realize that what you have provided is a short routine but how do I pu
it into my workbook?

THANKS AGAI
 
Hi mlomax,
Another way to do this without code is to compare the value in E10 to the value of the formula:

=IF(E10=yourformula,"Projected","Actual")

Keep in mind that in the possible case where the actual DOES equal the projected, the return will be wrong.

You could take this one step further and use conditional formatting with:
Cell Value Is
Equal to
yourformula
Click to expand...

Good Luck,
Mark Graesser
(e-mail address removed)
Boston MA

----- mlomax > wrote: -----

I am looking for a way to tell if a cell contains a formula or not.

I am writing a formula in one cell (say D10) that looks at another cell
(say E10) and determines if the E10 contains a formula. If E10 does
contain a formula, it would mean that the value in E10 is a projected
value and I would then place the word "Projected" into D10 indicating
the type of value located in E10. If, on the other hand, the user has
typed a value into E10 overwriting the formula that existed there
before, then I would set the value in D10 to "Actual".

Not only is this problem hard to research in Excel help but on this
site as well. It is hard to determine what search words to use to find
the problem. Therefore this may be documented somewhere else on this
site but I have been unable to find it.

Any help would be greatly appreciated.....


THANKS IN ADVANCE
 
Hi
try the following
- open your workbook
- Hit ALT F11 -> this open the VBA Editor
- create a new module (right click on your project in the left explorer
window)
- paste the code
- close the VBA editor
- save your workbook
 
Thanks for all of the replies. I did realize that I could retest t
determine if the formula results matched the cell value but it could b
possible that they would and the cell value would not be the product o
a formula. Also I need to repeat this many times and did not want t
alter the field after coping if possible. I will try Frank
suggestion and also look at JE's link.

THANKS AGAIN FOR ALL OF THE HELP
 
Back
Top