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