Conditional formatting offset reference

[IanC]

I have a spreadsheet with a block of 10 rows by 8 columns with conditional
formatting with absolute references to row 6.

I need to change these to reference row 7. I can edit each instance on one
row (16 in total) then copy the row down but, as the process may need to be
repeated in the future, can I use an offset reference instead of an absolute
reference, yet still retain the ability to copy the rows down?

For example, the formulae for cell E9 ore below
=VALUE(LEFT(E$6,2))*0.95
=VALUE(LEFT(E$6,2))*1.05

E$6 needs to be E$7.

Is there some way to say =VALUE(LEFT("current row -2",2))*0.95 in such a way
that when the row is copied down "current row-2" becomes "current row-3"?

 

[T. Valko]

For example, the formulae for cell E9 ore below

=VALUE(LEFT(E$6,2))*0.95
=VALUE(LEFT(E$6,2))*1.05

I don't understand that. A cell, E9, can contain only a single formula.

????

Is there some way to say =VALUE(LEFT("current row -2",2))*0.95
in such a way that when the row is copied down
"current row-2" becomes "current row-3"?

Yes, but I need to understand what you're trying to do, what cell the
formula is entered in and what cell you want to reference.

=VALUE(LEFT(E$6,2))*0.95

What's in cell E6? You may not need the VALUE function:

=LEFT(E$6,2)*0.95

 

[IanC]

Hi Biff

I don't understand that. A cell, E9, can contain only a single formula.

????

The first sentence of my original post referred to "conditional formatting".
These formulae are the limits for "Cell value is not between".

Yes, but I need to understand what you're trying to do, what cell the
formula is entered in and what cell you want to reference.


What's in cell E6? You may not need the VALUE function:

E6 is currently blank. The entire grid has moved down one row, which is why
I need to change the reference. E7 contains "60kV (20mAs)" and you are
correct. I don't need the VALUE in there.

=LEFT(E$6,2)*0.95

This replaces my original formula and works as long as the reference is in
row 6. As the reference cell is now in row 7, this needs to be
=LEFT(E$7,2)*0.95

I've tried playing with OFFSET and came up with
=LEFT(OFFSET(E9,-2,0),2)*0.95
This seems to work as I want it to (ie if I move the entire block down by 3
rows, the formula still references the reference cell (now 3 rows down as
well).

The drawback with this is that I can't edit one row then copy it down to the
other 9 rows. When I copy it down, the formula changes to
=LEFT(OFFSET(E10,-2,0),2)*0.95 whereas I need it to be
=LEFT(OFFSET(E10,-3,0),2)*0.95 (ie I need the row offset value to change
instead of the cell reference). I suppose an alternative would be to retain
the original formula in each row (ie =LEFT(OFFSET(E9,-2,0),2)*0.95), but can
I copy rows without changing the E9 reference without making the reference
absolute?

If this isn't possible, then it looks like I'm going to have to edit all 160
formulae manually.

 

[T. Valko]

If I understand...

You can make the offset relative like this:

=LEFT(OFFSET(E9,-ROWS(A$1:A2),0),2)*0.95

As you copy down you essentially get:

=LEFT(OFFSET(E9,-2,0),2)*0.95
=LEFT(OFFSET(E10,-3,0),2)*0.95
=LEFT(OFFSET(E11,-4,0),2)*0.95
=LEFT(OFFSET(E12,-5,0),2)*0.95
etc
etc