adding rows
 

How do I add rows without changing cell location in the formula.
For example:
The data for the formula is always located in cell B4 and if I
add a row on top of B4, I want the formula to remain with B4.
Right now, if I add a row, the cell in the formula goes down
one row.

Hope for help

If I understand, you're adding a row higher than B4. This shifts B4 down,
but you want the "new" B4 to be the data for the formula. If this is the
case you could try this:

=formula(OFFSET(A1,3,1,1,1))

That is, as long as you don't add a new top row.

Art

"Antoine" wrote:

How do I add rows without changing cell location in the formula.
For example:
The data for the formula is always located in cell B4 and if I
add a row on top of B4, I want the formula to remain with B4.
Right now, if I add a row, the cell in the formula goes down
one row.

Hope for help

Thank you Art.

that is exactly what I was looking for

Antoine

"Art" wrote:

If I understand, you're adding a row higher than B4. This shifts B4 down,
but you want the "new" B4 to be the data for the formula. If this is the
case you could try this:

=formula(OFFSET(A1,3,1,1,1))

That is, as long as you don't add a new top row.

Art

"Antoine" wrote:

How do I add rows without changing cell location in the formula.
For example:
The data for the formula is always located in cell B4 and if I
add a row on top of B4, I want the formula to remain with B4.
Right now, if I add a row, the cell in the formula goes down
one row.

Hope for help

A technique that survives adding a top row:

=formula(INDIRECT("B4"))

In article ,
Art wrote:

If I understand, you're adding a row higher than B4. This shifts B4 down,
but you want the "new" B4 to be the data for the formula. If this is the
case you could try this:

=formula(OFFSET(A1,3,1,1,1))

That is, as long as you don't add a new top row.

I like that better!

Art

"JE McGimpsey" wrote:

A technique that survives adding a top row:

=formula(INDIRECT("B4"))

In article ,
Art wrote:

If I understand, you're adding a row higher than B4. This shifts B4 down,
but you want the "new" B4 to be the data for the formula. If this is the
case you could try this:

=formula(OFFSET(A1,3,1,1,1))

That is, as long as you don't add a new top row.