I replicate all of what you describe, as you say that {#VALUE} is a very
subtle nuance indeed.

The exact same formula that produces the value error when evaluated in the
formula bar works fine in a CF formula. It's only when the used as part of
the AND formula it fails in the CF (though the same AND works in a cell).

My original comment about CF's only being able to reference cells on same
sheet was correct. However it's not the reason the OP's CF formula failed, a
CF formula can make use of Indirect perhaps as a workaround to reference a
cell on another sheet (new to me).

Regards,
Peter T


"Max" wrote in message
...
Peter, thanks for the thoughts

Yet I'm not sure it does for the reason you say,
or rather there is more to it.

It's perhaps just one of the subtle nuances in Excel

Supposing INDIRECT("'"&$B$3&"'!C"&ROW(A3))
resolves to {4}
and C3 contains: 4

Then placed in say, D4:
=C3=INDIRECT("'"&$B$3&"'!C"&ROW(A3))
will show the result correctly as TRUE

but when I select the expression in the formula bar and press F9
it shows the result there as: ={#VALUE!}, instead of TRUE

On the other hand, using this instead in D4:
=C3=INDIRECT("'"&$B$3&"'!C"&ROWS($1:1)+2)
will return TRUE both in the cell
and when we select the expression in the formula bar and press F9
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Peter T" <peter_t@discussions wrote in message
...
Hi Max,

I had another look and indeed your fix seems to work. Yet I'm not sure
it
does for the reason you say, or rather there is more to it.

I tried breaking up the formula:

Tom's 2nd part
=NOT(C3=INDIRECT("'"&$B$3&"'!C"&ROW(A3)))
it works, even with the ROW(A3)

your second part
=NOT(C3=INDIRECT("'"&$B$3&"'!C"&ROWS($1:1)+2))
it works

Check Tom's original again
=AND($J3="Change",NOT(C3=INDIRECT("'"&$B$3&"'!C"&R OW(A3))))
it fails, although both part evaluate to true

tried this one
=AND(TRUE,NOT(C3=INDIRECT("'"&$B$3&"'!C"&ROW(A3))) )
it fails, note the second part is same as first test above that works

As you say ROW(A3) returns an array {}, and so does all of this (select
in
the edit box & press F9)
INDIRECT("'"&$B$3&"'!C"&ROW(A3))

Perhaps the problem is the one element array within the AND formula, in
a
CF.

Small point, could change -
NOT(C3=INDIRECT(
to simply
C3<INDIRECT(

Regards,
Peter T