Detect If Cell Contains A Formula Or Constant

[misterspike]

I need an equation that lets me determine whether a cell contains a static constant value (either a string or a number), or an equation that generates a value.

Problem: I have a cell that contains a default equation that generates a default value, depending on a few conditions. The user can overwrite this cell with a constant. In another cell I need to know whether that cell is still the default equation or a new constant value.

I cannot simply test the value of the contents to see if they match my default, because the user may choose to enter that value.

Any help you can provide would be appreciated! Thanks in advance.

[Reafidy]

With vba you can use the hasformula property. Does that help?

[misterspike]

Unfortunately I cannot use VBA in this application because of my client's very restrictive security rules. Is there a way to get access to the same information as the "hasformula" property but with an equation?

Thanks!

[lessien]

You could go to "Edit > Go To > Special" and select formulas, constants, etc.

[misterspike]

But as I said I need to use this information in an IF statement in the formula of another cell. Any other ideas? Thanks!

[shg]

I don't know of a way to tell via a worksheet function whether a cell contains a formula.

[Wigi]

I just checked it, and it turns out that you can't check wheter the formula begins with an =

e.g.

=4+6

yields 1 with =LEFT(thatcell;1)

so this:

=(LEFT(A1;1)="=")

won't help us either.

Wigi

[Reafidy]

The user can overwrite this cell with a constant.

I would suggest finding an alternative the above situation. Eg a helper column where a user can insert a value then the formula will check if this helper column is blank and display the formula or constant if its not blank.

Like SHG I dont know of a "has formula" formula or equivalent.

[misterspike]

Right, eveything you try to do with the cell accesses the VALUE of the cell, not its formula. The closest I could find is the cell() function, but that will not work either. It can tell you if the value is formatted as a date or a percentage, but not if the cell contains a formula or a constant. Same with IsNumber(); it just tells you whether the VALUE of the cell is a number.

[Dave Hawley]

Detect If Cell Contains A Formula Or Constant

That would ANY cell with ANY data.

Formulae or Function cannot delete cells.