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

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

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