Conditional formatting offset reference
IanC
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"?
---
Ian
---
T. Valko
>=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
--
Biff
Microsoft Excel MVP
"IanC" <m...@me.com> wrote in message
news:NmCen.124864$kR2.1...@newsfe05.ams2...
IanC
"T. Valko" <biffi...@comcast.net> wrote in message
news:eseG3q4r...@TK2MSFTNGP05.phx.gbl...
> >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.
>
> ????
The first sentence of my original post referred to "conditional formatting".
These formulae are the limits for "Cell value is not between".
>
>>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:
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.
---
Ian
---
T. Valko
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
--
Biff
Microsoft Excel MVP
"IanC" <m...@me.com> wrote in message
news:6zNen.128157$kR2.1...@newsfe05.ams2...