Examining the Formula in a Cell

[Cyberdude]

I assume that there is no way using formulas to examine the contents of a formula (not the value) in a cell. Correct?
For example, the formula to be examined has a list of items that are AND'ed [AND(A1=2, A2>3,..., A10<12)], and I would like to be able to check to see if an item A6 is in that list. Can that be done with another formula somehow??

[Zack Barresse]

Can you explain a little more? Possibly give an example or two?

[Cyberdude]

It's a little hard to explain.

For example, the formula to be examined has a list of items that are AND'ed [AND(A1=2, A2>3,..., A10<12)], and I would like to be able to check to see if an item A6 is in that list.

I have a lot of workbooks that have a few nearly identical formulas that contain lists or sequences as I showed in the quote. For reasons I can't explain, sometimes the list for a particular workbook should have a cell reference in its list, but somehow along the way it got deleted or omitted. I would like to have a formula (ideally) that can make a continuing check of such lists to make sure they are correctly populated. If not, then activates an error message. I have used macros a few times to do this job, but they aren't running continuously. The ones I'm using execute from an event macro. I would just rather use a formula, but I very much doubt if it can be done. This was a stupid question. Don't soend any time on it. Thanks for the response.

[Zack Barresse]

I assume that there is no way using formulas to examine the contents of a formula (not the value) in a cell. Correct? ..

Correct.

What I would do is use 2 formulas (possibly coupled together if space is an issue). The first would be a UDF, something like this ...

Function FTEXT(f As Range)
FTEXT = f.Text
If f.HasFormula Then FTEXT = f.Formula
End Function

Which is derived from here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=62

With this UDF, you can then use the standard FIND function, something like this (assuming the FTEXT() function was used in, say, B1) ..

=FIND("A6",B1,1)

or, if you wanted to use error handling in your formula ...

=IF(ISERR(FIND("A6",B1,1)),"Nope","Yup")

Or you could incorporate all 3 into one ...

=IF(ISERR(FIND("A6",FTEXT(A1),1)),"Nope","Yup")

Is this what you mean Cyberdude? Hope I have understood you correctly. (And there's no such thing as a stupid question!!! )