If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional formatting offset reference
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"? --- Ian --- |
#2
|
|||
|
|||
Conditional formatting offset reference
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 -- Biff Microsoft Excel MVP "IanC" wrote in message ... 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"? --- Ian --- |
#3
|
|||
|
|||
Conditional formatting offset reference
Hi Biff
"T. Valko" wrote in message ... 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 --- |
#4
|
|||
|
|||
Conditional formatting offset reference
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 -- Biff Microsoft Excel MVP "IanC" wrote in message ... Hi Biff "T. Valko" wrote in message ... 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 --- |
Thread Tools | |
Display Modes | |
|
|