Sorry I forgot one more condition in 3(a) & 3(b):

What I require is to create a shortcut instead of a lengthy formula, which
shall:

1. Check the cell on the left;
2. Compare it with $F$4 (named "VM"); and
3. Return "TRUE" if:
.. (a) the cell on the left is a number AND greater than VM; OR
.. (b) the cell on the left is a number AND less than -VM; OR
.. (c) the cell on the left contains the text "old"; OR
.. (d) the cell on the left contains the text "new";

You are quite right that when named, it returns a #Value error, but when
inserted manually as a SIMPLE and not an ARRAY, it works correctly.

By the way, how to insert an array formula in a defined name?

Thanx again,
Best Regards,
Your ever-admiring Fan

FARAZ A. QURESHI


"Chip Pearson" wrote:

Without trying to figure out exactly what that formula is supposed to
do, it is worth noting that when a formula defined in a Name is
evaluated, it is always evaluated as if it were an array formula,
entered with CTRL SHIFT ENTER. (See
http://www.cpearson.com/excel/ArrayFormulas.aspx for lots more
information about array formulas.)

For example, if A1:A3 have the values 11, 22, and 33, the function

=SUM(LARGE($A$1:$A$3,ROW($1:$2)))

evaluates to 33 if it is not array entered but evaluates to 55 if it
is array entered. If you put that formula in a defined name, the Name
evaluates to 55, not 33, illustrating how Names are evaluated as array
formulas.

Your formula when array entered returns a #VALUE error, so I think the
task at hand now is to re-construct the formula in a manner such that
it will return the correct result when evaluated as an array formula.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Tue, 30 Dec 2008 00:01:04 -0800, FARAZ QURESHI
wrote:

=OR(AND(ISNUMBER(INDIRECT(ADDRESS(ROW(),COLUMN( )-1))),OR(INDIRECT(ADDRESS(ROW(),COLUMN()-1))=VM,INDIRECT(ADDRESS(ROW(),COLUMN()-1))<=-VM)),INDIRECT(ADDRESS(ROW(),COLUMN()-1))="Old",INDIRECT(ADDRESS(ROW(),COLUMN()-1))="New")