0

There are several other threads that seem to have addressed similar questions, but I am still unable to find a solution that works for me... I am using Excel 2016, and when I try to create a conditional formatting rule that uses AND(), INDIRECT(), and ADDRESS() functions the rule does not seem to work.

My rule applies to =$J:$BZ, and is the following:

=AND(INDIRECT(ADDRESS(ABS(ROW()-3),COLUMN(),4))>0,INDIRECT(ADDRESS(ROW(),9,3))="Example String")

I am simply trying to reference the value of a cell three rows above the current row (with the ABS() section simply to mitigate any errors if the row number becomes less than 1 for the first three rows) as well as the value of the fixed column (column "I" or 9).

This should be using A1 style, but I have also tried R1C1 style with no success... pulling this formula out of conditional formatting and entering it into the desired cell results in TRUE.

CJK
  • 99
  • 7
  • 20
  • 1
    In this particular case you can sidestep the use of row() and indirect() by using the fact that CF formulas wrap around from the end of the spreadsheet and use =AND(J108574>0,$I1="Example String") – Tom Sharpe Feb 25 '19 at 12:23
  • @TomSharpe thank you for the response! I didn't realize that conditional formatting wraps throughout the entire spreadsheet, so this was a much cleaner way to resolve my issue. – CJK Feb 25 '19 at 20:05

0 Answers0