Offset in Conditional Formatting?

[sly411]

I have the following formula to check for a condition:

=IF(CB3="Reg",MOD(CA3-BZ3,1)>TIME(0,$AG$39,0),IF(CB4="Reg",MOD(CA4-BZ4,1)>TIME(0,$AG$39,0),IF(CB5="Reg",MOD(CA5-BZ5,1)>TIME(0,$AG$39,0))))

Essentialy what it does is check if a value (Reg) is present in one of 3
sequential rows and if so calculates if the time difference between two
related values in the same row are greater than a predetermined number. If
TRUE then the cell is formatted. The formula works fine for a single cell
but when I use the Applies To field in CF and highlight the column I want to
apply it to, it increments the rows one at a time instead of jumping in
groups of 3 i.e. I want it to go to rows 6,7 and 8 next. Can I somehow use
Offset in the CF Applies To field, or is there another solution? Thanks

 

[Homey]

copy formula to 3 cells down then cut/paste back up 2.

|I have the following formula to check for a condition:
|
|
=IF(CB3="Reg",MOD(CA3-BZ3,1)>TIME(0,$AG$39,0),IF(CB4="Reg",MOD(CA4-BZ4,1)>TIME(0,$AG$39,0),IF(CB5="Reg",MOD(CA5-BZ5,1)>TIME(0,$AG$39,0))))
|
| Essentialy what it does is check if a value (Reg) is present in one of 3
| sequential rows and if so calculates if the time difference between two
| related values in the same row are greater than a predetermined number. If
| TRUE then the cell is formatted. The formula works fine for a single cell
| but when I use the Applies To field in CF and highlight the column I want
to
| apply it to, it increments the rows one at a time instead of jumping in
| groups of 3 i.e. I want it to go to rows 6,7 and 8 next. Can I somehow
use
| Offset in the CF Applies To field, or is there another solution? Thanks

 

[sly411]

The problem is not in copying cells in the spreadsheet. The problem is when
I put this formula into the Applies To field in Conditional Formatting. When
I highlight the cells that I want to apply the formatting to (they are
sequential rows in a column) the reference cells in the formula jump 1 at a
time and I need them to jumb 3 at a time.