Indirect in Conditional Formatting

[Tommy Boy]

I'm Stumped! I am trying to compare data from two worksheets and change the
formatting of the cells when the data does not match. Below are the three
formulas used in the conditional formatting:

Condition 1
=AND($C3>1,OR($J3="New",$J3="GAP"))
*This is set to turn the background color Red*
Condition 2
=OR(AND($C3=0,C2>0,$C4>0,$J3="Change"),AND(NOT($B3=$E3),NOT("COMBO-"&$B3=$E3),$E3>0,NOT($E3="Unassigned"),$J3="Change"))
*This is set to turn the background color Orange*
Condition 3
=AND($J3="Change",NOT(C3=INDIRECT("'"&$B$3&"'!C"&ROW(A3))))
*This is set to turn the background color Black*

My problem with this is in condition 3. This is supposed to first reference
J3, then compare the contents of cell C3 on the current worksheet with cell
C3 on a second worksheet. (Note: The name of the second worksheet is
contained in cell B3 on the current worksheet)

I can get that formula to result in a True/False when in a regular cell, but
when part of the conditional formatting, it doesn't seem to be working. This
will need to be dynamic because I will be copying it from C3 into 7,000 rows
below C3.

Any help would be great!

Tom

 

[Peter T]

Unfortunately CF formulas can only reference cells on the same sheet.

Regards,
Peter T

 

[Max]

Condition 3

=AND($J3="Change",NOT(C3=INDIRECT("'"&$B$3&"'!C"&ROW(A3))))

Try replacing the Condition 3 with this:
=AND($J3="Change",NOT(C3=INDIRECT("'"&$B$3&"'!C"&ROWS($1:1)+2)))

Above seems to works ok in testing here

Think the problem is more with ROW
ROW(A3) returns an array: {3} -- which doesn't seem to work
while ROWS($1:1)+2 returns a scalar: 3 -- which is ok

---

 

[Peter T]

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"&ROW(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

 

[Tommy Boy]

Thank you both for your help. I made some slight modifications since I part
of my macro deletes row 1, but everything is working great!

Thanks again!!

 

[Max]

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]

welcome, glad you got it up.

 

[Peter T]

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