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

 

[t0ny84]

Hi Marcie Be,

Looking at your formula you have
=OFFSET($U4,-MOD(ROW(U4),2),0)="Complete"
=OFFSET($U5,-MOD(ROW(U5),1),0)="Complete"

You would need to use
=OFFSET($U5,-MOD(ROW(U5),2),0)="Complete"


Try this and let me know how you go.
t0ny84

 

[Marcie Be]

t

Hi Marcie Be,

Looking at your formula you have
=OFFSET($U4,-MOD(ROW(U4),2),0)="Complete"
=OFFSET($U5,-MOD(ROW(U5),1),0)="Complete"

You would need to use
=OFFSET($U5,-MOD(ROW(U5),2),0)="Complete"


Try this and let me know how you go.
t0ny84

Thank you @t0ny84
=OFFSET($U5,-MOD(ROW(U5),2),0)="Complete"
produces

no CF happens

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

produces

I want to get back to this

how t use to work with =OFFSET($U4,-MOD(ROW(U4),2),0)="Complete" before I inserted a row above Row 4 making it Row 5.

Any other suggests? All welcome.

 

[t0ny84]

Are you using =OFFSET($U4,-MOD(ROW(U4),2),0)="Complete" in your Conditional Formatting or is this in the cell itself?

 

[Marcie Be]

Conditional Format formula

 

[t0ny84]

=OFFSET($U5,-MOD(ROW(U4),2),0)="Complete"
and applied to =$U$5:$U$6

 

[Marcie Be]

=OFFSET($U5,-MOD(ROW(U4),2),0)="Complete"
and applied to =$U$5:$U$6
View attachment 21237

@t0ny84 Pefect!! You are a STAR!!
I always like to understand Why and not just because.
Any chance you can explain to me the logic? That way I get to learn.
Thanks again, much appreciated.
Marcie

 

[t0ny84]

@t0ny84 Pefect!! You are a STAR!!
I always like to understand Why and not just because.
Any chance you can explain to me the logic? That way I get to learn.
Thanks again, much appreciated.
Marcie

I'm sorry I can't explain the logic because I myself don't understand the why with this one! Hopefully someone with more advanced skills can shed more light on the why.

 

[Marcie Be]

I'm sorry I can't explain the logic because I myself don't understand the why with this one! Hopefully someone with more advanced skills can shed more light on the why.

@t0ny84 Thanks again.
Excel formatting is tricky to get your head round sometimes, I know. Thanks again.
Regards
Marcie

 

[Eric W]

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!