Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect in Conditional Formatting
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($C31,OR($J3="New",$J3="GAP")) *This is set to turn the background color Red* Condition 2 =OR(AND($C3=0,C20,$C40,$J3="Change"),AND(NOT($B3 =$E3),NOT("COMBO-"&$B3=$E3),$E30,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"&R OW(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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect in Conditional Formatting
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect in Conditional Formatting
Condition 3
=AND($J3="Change",NOT(C3=INDIRECT("'"&$B$3&"'!C"&R OW(A3)))) Try replacing the Condition 3 with this: =AND($J3="Change",NOT(C3=INDIRECT("'"&$B$3&"'!C"&R OWS($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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tommy Boy" wrote: 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($C31,OR($J3="New",$J3="GAP")) *This is set to turn the background color Red* Condition 2 =OR(AND($C3=0,C20,$C40,$J3="Change"),AND(NOT($B3 =$E3),NOT("COMBO-"&$B3=$E3),$E30,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"&R OW(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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect in Conditional Formatting
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 "Max" wrote in message ... Condition 3 =AND($J3="Change",NOT(C3=INDIRECT("'"&$B$3&"'!C"&R OW(A3)))) Try replacing the Condition 3 with this: =AND($J3="Change",NOT(C3=INDIRECT("'"&$B$3&"'!C"&R OWS($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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tommy Boy" wrote: 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($C31,OR($J3="New",$J3="GAP")) *This is set to turn the background color Red* Condition 2 =OR(AND($C3=0,C20,$C40,$J3="Change"),AND(NOT($B3 =$E3),NOT("COMBO-"&$B3=$E3 ),$E30,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"&R OW(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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect in Conditional Formatting
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" wrote: Condition 3 =AND($J3="Change",NOT(C3=INDIRECT("'"&$B$3&"'!C"&R OW(A3)))) Try replacing the Condition 3 with this: =AND($J3="Change",NOT(C3=INDIRECT("'"&$B$3&"'!C"&R OWS($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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tommy Boy" wrote: 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($C31,OR($J3="New",$J3="GAP")) *This is set to turn the background color Red* Condition 2 =OR(AND($C3=0,C20,$C40,$J3="Change"),AND(NOT($B3 =$E3),NOT("COMBO-"&$B3=$E3),$E30,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"&R OW(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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect in Conditional Formatting
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect in Conditional Formatting
welcome, glad you got it up.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tommy Boy" wrote in message ... 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!! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect in Conditional Formatting
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Indirect for conditional Formating | Excel Worksheet Functions | |||
Indirect - how to ensure the formatting is transferred | Excel Discussion (Misc queries) | |||
Conditional Formatting: Logical AND fails to work with INDIRECT(.. | Excel Worksheet Functions | |||
conditional indirect with data range | Excel Worksheet Functions | |||
Conditional or Indirect fill up | Excel Discussion (Misc queries) |