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

=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

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")

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

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")

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")

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")

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")

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

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

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

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