Formula to determine whether a cell contains a formula, as opposed to a constant

[ipsyd]

I seen to recall that years ago, I figured out a formula (just using worksheet functions, not VBA) that would return TRUE if the cell referenced by the formula contained a formula. Put another way, it returned TRUE if the first character of the actual contents (not value) of the referenced cell was "=".

Can anyone jog my fading memory?

 

[Joe4]

It looks like the newer versions of Excel have a function named FORMULATEXT that you can use for this: https://support.office.com/en-us/article/FORMULATEXT-function-0a786771-54fd-4ae2-96ee-09cda35439c8

 

[Jonmo1]

Prior to the inclusion of the FORMULATEXT function in XL2013, the only method I've been aware of with formulas is the Get.Cell function used in Name Manager
Here's a link with some information about that
Keep in mind, it's technically still a macro, and will require macros to be enabled.

 

[AkaTrouble]

depending on why you need to know a simple quick method to see cells formula filled cells is to to press [CTRL] + [¬] (tilda key hich is the key on top row to left of the [1] key.)

this will show you the cells displaying only formula's.

might not help what your need is but is offered as option

regards

EDIT

the is also formula ISFORMULA example with forumula in A1 this formula in B1 returns true no formula = false

=ISFORMULA(A1)

 

[T. Valko]

the is also formula ISFORMULA example with forumula in A1 this formula in B1 returns true no formula = false

=ISFORMULA(A1)

Note that the ISFORMULA function requires the use of Excel 2013 or later.

 

[AkaTrouble]

Note that the ISFORMULA function requires the use of Excel 2013 or later.

yepp should of said that only added it as after thought when reading back as OP had not offered version or OS

 

[ipsyd]

Thanks Joe4. However, for various reasons (mainly vastly superior interface IMO) I'm using Excel for Mac 2011, and while Microsoft's website says that FORMULATEXT works in XL Mac 2011, I've tested it, and unfortunately it doesn't: it returns a NAME? error. So I'm out of luck with that.

Jonmo1, your link was a gold mine! I'm an old-timer (started with Excel in 1986, before Excel for Windows even existed) so I'm well aware of the old XL Macro language: in fact, use it all the time in preference to VBA. The solution in the link you referred to looked like it would be hard to use: in essence, you have to define a function using the old GET.CELL function, but in the absence of an OFFSET, the defined function is trivial, because it always returns TRUE for any cell you use it in.

Very very fortunately, I didn't need to use my CellContainsFormula function in the cell itself, but in a Conditional Formatting formula. My overall objective was to use a single CF rule for hundreds of cells: each would be shaded green if it contained a constant instead of a formula. And this worked magnificently!!

For the benefit of others –
1. In Insert > Name > Define... I defined the name "CellHasFormula" to be "=GET.CELL(48,INDIRECT("RC",FALSE))".
2. I created a Conditional Formatting rule that used the following formula to determine whether to apply the shading: "=NOT(CellHasFormula)", and applied that CF rule to hundreds of cells.
3. Now, every time a user replaces the existing formula in any of those cells with a constant, the cell is highlighted!

Victory! I'm stoked!! Thanks Jonmo1 and PaddyD!!!

 

[ipsyd]

Later posters: thanks too for your input. But like FORMULATEXT, ISFORMULA wasn't a solution for me. Sorry to waste your time: I'll include OS and XL version in future.

 

[Jonmo1]

Glad to help, thanks for the feedback.