Weird behavior with conditional formatting-CodePudding

I have added the following two equations to conditional formatting:

Green: =IF(REGEXMATCH(VLOOKUP(X2, INDIRECT("DEALS!$A$2:F"),5, FALSE), "Likes"), R2>=VLOOKUP(X2, INDIRECT("DEALS!$A$2:F"), 4, FALSE), T2>=VLOOKUP(X2, INDIRECT("DEALS!$A$2:F"), 4, FALSE))

Red: =IF(REGEXMATCH(VLOOKUP(X2, INDIRECT("DEALS!$A$2:F"),5, FALSE), "Likes"), NOT(R2>=VLOOKUP(X2, INDIRECT("DEALS!$A$2:F"), 4, FALSE)), NOT(T2>=VLOOKUP(X2, INDIRECT("DEALS!$A$2:F"), 4, FALSE)))

The colors should change accordingly depending on whether the target (views in this case) has been met or not.

Below I have also added the equation into the cells to check if the logic is correct, which it appears to be (left = green logic, right = red logic).

For whatever reason, the first row, despite the target not being met, has decided to select the green color. The row below that is doing the complete opposite. And to top it all off, the last two rows are not selecting a color at all even though I have applied the conditional formatting to the entire column:

I am also experiencing weird behavior when dragging equations within this P column, but do not see this same behavior in other columns that also use conditional formatting:

CodePudding user response：

do not lock ($) references inside INDIRECT. if stuff is between double quotes it's a text string, not an active reference, and text strings are not affected by dragging.

for green use:

=IF(REGEXMATCH(VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 5, 0), "Likes"), 
 R2>=VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 4, 0), 
 T2>=VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 4, 0))

for red use:

=IF(REGEXMATCH(VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 5, 0), "Likes"),   
 NOT(R2>=VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 4, 0)),   
 NOT(T2>=VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 4, 0)))

CodePudding user response：

As suggested, currently using the following equation on the first row that auto-populates the column and therefore dragging he formula is no longer required:

`=ARRAYFORMULA(IFNA(TEXT(VLOOKUP(Y2:Y,DEALS_RNG,COLUMN(DEALS!$E$1)-COLUMN(DEALS!$A$1) 1,0), "#,###,##0")& " " &VLOOKUP(Y2:Y,DEALS_RNG,COLUMN(DEALS!$F$1)-COLUMN(DEALS!$A$1) 1,0)))`

This, in combination with the two correct formulas making use of the INDIRECT() function, have so far resulted in no issues.

Green:

`=IF(REGEXMATCH(VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 5, 0), "Likes"), R2>=VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 4, 0), T2>=VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 4, 0))`

Red:

`=IF(REGEXMATCH(VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 5, 0), "Likes"), NOT(R2>=VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 4, 0)), NOT(T2>=VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 4, 0)))`

Thanks for the help!

Will update this answer if anything changes.