OFFSET Formula no longer working due to addition of row into worksheet

[Marcie Be]

I had the following CF Formula

=OFFSET($U4,-MOD(ROW(U4),2),0)="Complete"

Problemis I have added a row to the worksheet.

Previously when U4 changed to complete it would fill green along with U5

Now that I have added a row above my original row 4, U4 is now U5 and U5 is now U6

I modified the formula to

=OFFSET($U5,-MOD(ROW(U5),1),0)="Complete"

But this only fills U5 green when it contains the word complete. U6 no longer fills green.

What further formula changes do I need to make?

@Eric W you had helped me with this previously, if you could help again that would be much appreciated.

Thanks in advance

Marcie

 

[Marcie Be]

Here's the logic behind it. In row 5, ROW(U4) equals 4. The MOD function gives you the remainder when you divide by 2. 4/2 has no remainder, so that's 0. Then the OFFSET function starts at $U5, has a row offset of -0, so it resolves to U5, then it checks U5 to see if it says "Complete".

Now in row 6, the formula adapts to ROW(U5). 5 mod 2 = 1, so the OFFSET starts at $U6 this time, then applies the -1 row offset, to get back to the U5 cell to see if it says "Complete".

I'd actually recommend a different formula. Select U5:U6, click Conditional Formatting, and use this formula:

=$U$5="Complete"

With the absolute reference on the row, the formula won't change in the U6 cell. This also will continue to work if you add/delete rows above the formula. If you apply this to a larger range than 2 cells, we might need to go back to OFFSET, but see how that works.

Good luck!

Wow.
Fantastic explanation. The development of this Tracking document is causing me to learn so many things about excel.
Thank you. Learning through doing. I love it