Static cell reference

L

Luc Benninger

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
 
L

Luc Benninger

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
 
J

Jack Sheet

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.
 
L

LanceB

How about
=MOD(ROW()-1,7)<3

Luc Benninger said:
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
Click to expand...
 
L

Luc Benninger

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.
 
L

Luc Benninger

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.
 
J

Jack Sheet

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
 
Top