Preserving Cell References

[Daiv]

I have a formula that I wrote up and it works great. the formula is dragged
down multiple cells, and the cell references update accordingly.

My problem is when I add a new row, the cell references update again. for
example, if I add a new row between 10 and 11, anything referencing the old
11 row would automatically change to reference 12.

I tried using indirect() but then when i drag the formula down the cells
don't update the references. (which is also neccesary)

For example, if cell B11 is =A12, I should be able to drag it down to B12
and it should equal =A13. also, if I add a new row in between, all
references to A12 should remain =A12 and not change to =A13.

Any ideas?

Thanks!

[Elkar]

You may want to look into using the OFFSET function.

In B11 enter: =OFFSET(B11,1,-1)

This references the cell that is one row down, and one column to the left of
the current cell (A12 in this case). Thus, this reference will not change if
new rows are added.

HTH,
Elkar


"Daiv" wrote:

> I have a formula that I wrote up and it works great. the formula is dragged
> down multiple cells, and the cell references update accordingly.
>
> My problem is when I add a new row, the cell references update again. for
> example, if I add a new row between 10 and 11, anything referencing the old
> 11 row would automatically change to reference 12.
>
> I tried using indirect() but then when i drag the formula down the cells
> don't update the references. (which is also neccesary)
>
> For example, if cell B11 is =A12, I should be able to drag it down to B12
> and it should equal =A13. also, if I add a new row in between, all
> references to A12 should remain =A12 and not change to =A13.
>
> Any ideas?
>
> Thanks!

[Ron Coderre]

Try something like in this example:

A1: 10
A2: =OFFSET(A2,-1,0)
(A2 will return 10)

Insert a row above A2
(The formula that was in A2 is now in A3...but it refers to the cell
directly above A3)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Daiv" wrote:

> I have a formula that I wrote up and it works great. the formula is dragged
> down multiple cells, and the cell references update accordingly.
>
> My problem is when I add a new row, the cell references update again. for
> example, if I add a new row between 10 and 11, anything referencing the old
> 11 row would automatically change to reference 12.
>
> I tried using indirect() but then when i drag the formula down the cells
> don't update the references. (which is also neccesary)
>
> For example, if cell B11 is =A12, I should be able to drag it down to B12
> and it should equal =A13. also, if I add a new row in between, all
> references to A12 should remain =A12 and not change to =A13.
>
> Any ideas?
>
> Thanks!

[Daiv]

Thanks!

Using Offset was the trick. this is what my final formula looks like.

"=IF((OR(OFFSET(B11,-1,0)="RB",OFFSET(B11,-1,0)="LB")),IF(OFFSET(I11,-1,0)="VERT",(ABS(IF(OFFSET(B11,1,-1)>0,OFFSET(B11,1,-1),OFFSET(B11,2,-1))-OFFSET(B11,0,-1))/2+ABS(OFFSET(B11,0,-1)-OFFSET(B11,-1,-1))),ABS(OFFSET(B11,-1,-1)-IF(OFFSET(B11,1,-1)>0,OFFSET(B11,1,-1),OFFSET(B11,2,-1)))/2),IF((OR(OFFSET(B11,1,0)="RB",OFFSET(B11,1,0)="LB")),IF(OFFSET(I11,1,0)="VERT",(ABS(IF(OFFSET(B11,-1,-1)>0,OFFSET(B11,-1,-1),OFFSET(B11,-2,-1))-OFFSET(B11,0,-1))/2+ABS(OFFSET(B11,0,-1)-OFFSET(B11,1,-1))),ABS(OFFSET(B11,1,-1)-IF(OFFSET(B11,-1,-1)>0,OFFSET(B11,-1,-1),OFFSET(B11,-2,-1)))/2),ABS(IF(OFFSET(B11,-1,-1)>0,OFFSET(B11,-1,-1),OFFSET(B11,-2,-1))-IF(OFFSET(B11,1,-1)>0,OFFSET(B11,1,-1),OFFSET(B11,2,-1)))/2))"


A mess, but a mess that works.

Daiv

"Daiv" wrote:

> I have a formula that I wrote up and it works great. the formula is dragged
> down multiple cells, and the cell references update accordingly.
>
> My problem is when I add a new row, the cell references update again. for
> example, if I add a new row between 10 and 11, anything referencing the old
> 11 row would automatically change to reference 12.
>
> I tried using indirect() but then when i drag the formula down the cells
> don't update the references. (which is also neccesary)
>
> For example, if cell B11 is =A12, I should be able to drag it down to B12
> and it should equal =A13. also, if I add a new row in between, all
> references to A12 should remain =A12 and not change to =A13.
>
> Any ideas?
>
> Thanks!