Formula to detect formulas?

**djsquare** · 10-13-2011, 10:41 AM

Hi all,

Without using VB or some script, is there a way to determine if a cell contains a hard coded numeric value versus a formula that is determining a value? I need to perform certain actions if a cell is hard inputted, and certain other actions if it contains a formula. Any ideas?

Thank you!
Dan

**quekbc** · 10-13-2011, 10:54 AM

Try this.

Define a new name. (Insert, Name, Define or Formulas --> Name Manager)

Choose a name, say, IsFormula
And type this in the RefersTo section

Please Login or Register  to view this content.

This will return TRUE if there is a formula in A1, FALSE if its a hardcoded number/text/blank

**Whizbang** · 10-13-2011, 10:57 AM

http://www.ozgrid.com/forum/showthre...t=80337&page=1

After doing some Googling, it looks like this is not possible without VBA.

You could do something like this:

=CELL("type",A1)

But that will only tell you if the cell is blank, text, or something else.

**quekbc** · 10-13-2011, 11:00 AM

More description to my post above.

In any cell if you type in

Please Login or Register  to view this content.

It will show TRUE if A1 is a formula, FALSE if its a hardcoded number/text/blank

**Whizbang** · 10-13-2011, 11:00 AM

quekbc,

That's pretty neat. I am not sure if this will work for the OP, because it would require making a named formula for each cell he wants to check, but I certainly am happy to have learned of this little trick (though I can't think of a practical application at the moment).

One thing, why does it work as a named formula but not as a formula on the worksheet?

**Edit**

Also tried it as a conditional format formula, but no go. Wierd.

**djsquare** · 10-13-2011, 11:06 AM

This is a good start - thank you! Unfortunately, this looks like since it's defining a name for one specific cell, it can only be used on one cell. I suppose I should have specified I need to run this test on numerous cells throughout a model. Any way to use this approach but specify which cells to test?

Thanks!

**Richard Buttrey** · 10-13-2011, 11:07 AM

Hi,

Why the reluctance to use a macro or a UDF?
In this case it would be much simpler.

Regards

**quekbc** · 10-13-2011, 11:10 AM

In that case,

If there is a pattern to where you put your formulas, say for example, you want to test whether the value in column E is a formula or not, you can use...

Please Login or Register  to view this content.

Or say, if you want the to use the IsFormula function immediately to the right of the value in question, you can use,

Please Login or Register  to view this content.

Originally Posted by Whizbang

One thing, why does it work as a named formula but not as a formula on the worksheet?

**Edit**

Also tried it as a conditional format formula, but no go. Wierd.

From what I know, the Get.Cell function is an old school macro function (xl4). This function has been removed from Excel but we can still "cheat" excel into using it and that is by using named ranges to type it in.

**quekbc** · 10-13-2011, 11:13 AM

*deleted* *deleted*

**Whizbang** · 10-13-2011, 11:17 AM

Brilliant, quekbc!

Here is a link that give some great info on this method:
http://www.mrexcel.com/forum/showthread.php?t=20611

**Whizbang** · 10-13-2011, 11:25 AM

I had to add quotes to your formula to get it to work:

=GET.CELL(48,INDIRECT("Sheet1!RC[-1]",FALSE))

**MarvinP** · 10-13-2011, 11:27 AM

I guess using Show Formulas is no use for the OP?
http://spreadsheets.about.com/od/exc...w_formulas.htm

Also using PUP found at http://spreadsheetpage.com/index.php/pupv7/home
has a utility to map a spreadsheet and determine if a cell is a value or formula.

**quekbc** · 10-13-2011, 11:29 AM

Originally Posted by Whizbang

I had to add quotes to your formula to get it to work:

=GET.CELL(48,INDIRECT("Sheet1!RC[-1]",FALSE))

Oh, yes. You're right. Missed that last bit. Thanks!

**Whizbang** · 10-13-2011, 11:36 AM

MarvinP,
The OP didn't say, but I can see where having a formula to determine this can be useful, either as conditional formatting, or to combine with an IF() function and cause certain calculations or text to appear.

Also, the OP could use the Go To Special dialog box to select constants or formulas.

**romperstomper** · 10-13-2011, 11:50 AM

Originally Posted by Whizbang

I had to add quotes to your formula to get it to work:

=GET.CELL(48,INDIRECT("Sheet1!RC[-1]",FALSE))

If you remove the sheet name, then it will work on any sheet (which is really the point of using INDIRECT):

=GET.CELL(48,INDIRECT("RC[-1]",FALSE))

for example to do the cell to the left of wherever you enter it.

**ChemistB** · 10-13-2011, 03:10 PM

I've used the following in conjunction with Conditional Formatting

ISFORMULA =GET.CELL(48,INDIRECT("rc",FALSE))

Then select the entire sheet or used range and in conditional formatting
using formula = IsFormula Format as needed.

Formula to detect formulas?

LinkBack

Thread Tools

Rate This Thread

Display