How to get the indirect formula to not return an absolute cell reference

I'm trying to use an indirect formula in a conditional format formula and I'd like to control the $ references that are returned by the indirect formula.

=AND(ISNUMBER(MATCH("Not Interested",$16:$16,0)),INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH("Not 
Interested",$16:$16,0),4),"1","")&ROW(J17)))

Essentially, I'm looking for the row that contains the "Not interested" column header and then I want to format the entire row red if that column has an "x" in it.

INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH("Not Interested",$16:$16,0),4),"1","")&ROW(J17)))

The above part of my formula is correctly finding the row and using row(J17) will allow me to move through my cells in the conditional format formula but the only problem is Indirect(K17) returns $K$17 when i need it to return $K17 or even just K17 is there a way to change that?

Solution 1:

No need to use indirect at all if you use absolute references judiciously:

=INDEX(17:17,MATCH("Not interested",$16:$16,0))="x"

Solution 2:

Conditional Formatting is evaluated independently for every cell in the "Applies To" range. You want to write the formula so that it'll work for every cell in that range.

=INDIRECT("R"&ROW()&"C"&MATCH("Not Interested",$16:$16,0),FALSE)="x"