Test if Cell Contents is Formula

[ecl]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Is there a way to test whether the cell contents is a formula (vs. blank, a number, or text)?

I want to use this for conditional formatting so I can easily tell input cells from results cells in a big worksheet.

Thx
Ed

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Is there a way to test whether the cell contents is a formula (vs. blank, a
number, or text)?

I want to use this for conditional formatting so I can easily tell input cells
from results cells in a big worksheet.

Thx
Ed

That's a little iffy. My suggestion is to use LEN to test the cell's length.
If zero, the cell is blank. You could use ISTEXT or ISNUMBER to see if the
contents (or result of a formula in the cell) is text or a number. To see if
the cell contains a formula is somewhat messy. One way is to do the
following: Define a name, "XXX" (or whatever) and have it refer to
"=Get.cell(6)". Then define another name "YYY" and have it refer to
"=get.cell(7)". Then test to see if xxx=yyy. If they are equal, then the
cell does NOT contain a formula.

This will probably work, but do not try to copy the worksheet. Saving and
reopening the workbook should be fine.

 

[CyberTaz]

"Iffy" ain't the half of it I'm not challenging you in any way, Ed, but
I can't conceive of any situation where I would construct a worksheet where
the nature of the content would be expected to vary... Updated yes, but for
any one cell to contain a formula one day, a text string another day & be
blank the next is just beyond my comprehension. Perhaps I'm misunderstanding
your query, but that's the only purpose conditional formatting would
accomplish. If I'm properly interpreting your request -- no offense
intended -- I think I'd be tempted to reconsider how I'm setting things up.

If you can describe your intent in more detail perhaps some other
alternatives would be helpful.

 

[ecl]

Hello Bob,

You are correct. I'm not using it apply conditional formatting that I expect to change from day to day. This would simply be an easier way to format a large worksheet, particularly one that has been added on to over a long period of time or perhaps one that I didn't create, to quickly reveal the input cells.

Now if we were all highly organized structured programmers I'd say you are right, and there'd be no point because we all design our worksheets with excellent clarity. But we're not, and I'm not so this would be a truly useful tool.

Now, what's funny is the TYPE function is 'supposed' to be able to do this I think. In the dialog box for selecting a function if you select TYPE it shows that TYPE() = 8 indicates that the reference cell is a function. But if you go to the full Excel Help page for TYPE it skips this output in the list:

If value is, TYPE returns
Number 1
Text 2
Logical value 4
Error value 16
Array 64

It's almost as if they intended to do it, and someone figured out it wasn't so easy since the identifying a function is "pre-evaluation" whereas all the others are after what's in the cell has been evaluated.