Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Static cell reference
Hi,
I should create a formula with kind of a static cell reference within. That means, if cells are deleted or inserted above or to the left of the referenced cell, the reference should still point to the same cell as before and not be automatically adjusted. Is this possible?? Thanks, Luc |
#2
|
|||
|
|||
=INDIRECT("A1")
-- HTH Bob Phillips "Luc Benninger" wrote in message ... Hi, I should create a formula with kind of a static cell reference within. That means, if cells are deleted or inserted above or to the left of the referenced cell, the reference should still point to the same cell as before and not be automatically adjusted. Is this possible?? Thanks, Luc |
#3
|
|||
|
|||
Thanks for replying Bob. I wasn't able to use the INDIRECT function in
my formula. Maybe you are? I need this two conditional format formulas to format a selected range: =MOD(ROW(A7001)-1;7)<3 =MOD(ROW(A7001)-1;7)=3 These formulas mark the first three rows in a colour, the next four in an other colour, then again three rows in the first colour and so on and on. If a user deletes the referenced row the conditional format won't work anymore (lost ref). Therefore I use row 7001, hoping that nobody (or hardly anybody) will ever delete it. But there is still a problem. If above the referenced row a row is deleted or inserted, the conditional formulas change automatically (i.e. to =MOD(ROW(A7002)-1;7)<3). Now the colour pattern on the selected range shifts vertically by one position. Therefore I would like to have the "A7001"-reference to be static. Thanks again, Luc Bob Phillips wrote: =INDIRECT("A1") |
#4
|
|||
|
|||
Don't know if this helps, but
In cell B10 if you enter =OFFSET(B10,-2,3) Then it will always refer to the cell 2 up and 3 to the right, regardless of whether you insert or delete rows or columns between the referenced cell and the referencing cell. "Luc Benninger" wrote in message ... Hi, I should create a formula with kind of a static cell reference within. That means, if cells are deleted or inserted above or to the left of the referenced cell, the reference should still point to the same cell as before and not be automatically adjusted. Is this possible?? Thanks, Luc |
#5
|
|||
|
|||
Luc,
Why not just use =MOD(ROW()-1;7)<3 =MOD(ROW()-1;7)=3 -- HTH Bob Phillips "Luc Benninger" wrote in message ... Thanks for replying Bob. I wasn't able to use the INDIRECT function in my formula. Maybe you are? I need this two conditional format formulas to format a selected range: =MOD(ROW(A7001)-1;7)<3 =MOD(ROW(A7001)-1;7)=3 These formulas mark the first three rows in a colour, the next four in an other colour, then again three rows in the first colour and so on and on. If a user deletes the referenced row the conditional format won't work anymore (lost ref). Therefore I use row 7001, hoping that nobody (or hardly anybody) will ever delete it. But there is still a problem. If above the referenced row a row is deleted or inserted, the conditional formulas change automatically (i.e. to =MOD(ROW(A7002)-1;7)<3). Now the colour pattern on the selected range shifts vertically by one position. Therefore I would like to have the "A7001"-reference to be static. Thanks again, Luc Bob Phillips wrote: =INDIRECT("A1") |
#6
|
|||
|
|||
How about
=MOD(ROW()-1,7)<3 "Luc Benninger" wrote: Thanks for replying Bob. I wasn't able to use the INDIRECT function in my formula. Maybe you are? I need this two conditional format formulas to format a selected range: =MOD(ROW(A7001)-1;7)<3 =MOD(ROW(A7001)-1;7)=3 These formulas mark the first three rows in a colour, the next four in an other colour, then again three rows in the first colour and so on and on. If a user deletes the referenced row the conditional format won't work anymore (lost ref). Therefore I use row 7001, hoping that nobody (or hardly anybody) will ever delete it. But there is still a problem. If above the referenced row a row is deleted or inserted, the conditional formulas change automatically (i.e. to =MOD(ROW(A7002)-1;7)<3). Now the colour pattern on the selected range shifts vertically by one position. Therefore I would like to have the "A7001"-reference to be static. Thanks again, Luc Bob Phillips wrote: =INDIRECT("A1") |
#7
|
|||
|
|||
This would not assure that the first three lines of the range selection
are in color1 and the next four in color2. The pattern would depend on the first row number in the selection. LanceB wrote: How about =MOD(ROW()-1,7)<3 "Luc Benninger" wrote: Thanks for replying Bob. I wasn't able to use the INDIRECT function in my formula. Maybe you are? I need this two conditional format formulas to format a selected range: =MOD(ROW(A7001)-1;7)<3 =MOD(ROW(A7001)-1;7)=3 These formulas mark the first three rows in a colour, the next four in an other colour, then again three rows in the first colour and so on and on. If a user deletes the referenced row the conditional format won't work anymore (lost ref). Therefore I use row 7001, hoping that nobody (or hardly anybody) will ever delete it. But there is still a problem. If above the referenced row a row is deleted or inserted, the conditional formulas change automatically (i.e. to =MOD(ROW(A7002)-1;7)<3). Now the colour pattern on the selected range shifts vertically by one position. Therefore I would like to have the "A7001"-reference to be static. Thanks again, Luc Bob Phillips wrote: =INDIRECT("A1") |
#8
|
|||
|
|||
Then how about =MOD(ROW(A1)-1,7)<3
-- HTH Bob Phillips "Luc Benninger" wrote in message ... This would not assure that the first three lines of the range selection are in color1 and the next four in color2. The pattern would depend on the first row number in the selection. LanceB wrote: How about =MOD(ROW()-1,7)<3 "Luc Benninger" wrote: Thanks for replying Bob. I wasn't able to use the INDIRECT function in my formula. Maybe you are? I need this two conditional format formulas to format a selected range: =MOD(ROW(A7001)-1;7)<3 =MOD(ROW(A7001)-1;7)=3 These formulas mark the first three rows in a colour, the next four in an other colour, then again three rows in the first colour and so on and on. If a user deletes the referenced row the conditional format won't work anymore (lost ref). Therefore I use row 7001, hoping that nobody (or hardly anybody) will ever delete it. But there is still a problem. If above the referenced row a row is deleted or inserted, the conditional formulas change automatically (i.e. to =MOD(ROW(A7002)-1;7)<3). Now the colour pattern on the selected range shifts vertically by one position. Therefore I would like to have the "A7001"-reference to be static. Thanks again, Luc Bob Phillips wrote: =INDIRECT("A1") |
#9
|
|||
|
|||
Then I run into trouble if someone deletes the first row. the
conditional format formula will have a ref error and no formatting at all will be applied. Bob Phillips wrote: Then how about =MOD(ROW(A1)-1,7)<3 |
#10
|
|||
|
|||
That doesn't happen for me. Which row has the error?
-- HTH Bob Phillips "Luc Benninger" wrote in message ... Then I run into trouble if someone deletes the first row. the conditional format formula will have a ref error and no formatting at all will be applied. Bob Phillips wrote: Then how about =MOD(ROW(A1)-1,7)<3 |
#11
|
|||
|
|||
I get it now.
Final shot <vbg =MOD(ROW(1:1)-1,7)<3 -- HTH Bob Phillips "Luc Benninger" wrote in message ... Then I run into trouble if someone deletes the first row. the conditional format formula will have a ref error and no formatting at all will be applied. Bob Phillips wrote: Then how about =MOD(ROW(A1)-1,7)<3 |
#12
|
|||
|
|||
Why not simply have the conditional format refer to the same cell (or a cell
in the same row) as the cell containing the conditional format? Provided that you exclude "$" symbols ie ensure that the cells within the conditional format are all expressed as relative, then the conditional format function should still work if you copy the cell (or just copy special the formats) down. Then any row that is deleted should not give rise to any error message, and the row colouring should be preserved. Thus, in cell A1, use =MOD(ROW(A1)-1,7)<3 but in cell A2, use =MOD(ROW(A2)-1,7)<3 etc -- Return email address is not as DEEP as it appears "Luc Benninger" wrote in message ... Then I run into trouble if someone deletes the first row. the conditional format formula will have a ref error and no formatting at all will be applied. Bob Phillips wrote: Then how about =MOD(ROW(A1)-1,7)<3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
up to 7 functions? | Excel Worksheet Functions | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Formula with text and reference to a date cell | Excel Discussion (Misc queries) | |||
international absulute cell reference | Excel Discussion (Misc queries) | |||
Using the results from two seperate cells to create cell reference | Excel Worksheet Functions |