Copying Formulas

[LRL]

My question concerns copying a formula in a cell that refers to another cell
using relative references, then pasting that cell and it's formula to another
cell and maintaining the correct relationships.

Suppose cell (10,10) refers to cell (9,9) using formula =R[-1]C[-1].

If I insert a column and row between cell (10.10) and cell (9.9), the
spreadsheet correctly changes the formual to =R[-2]C[-2]. But if I select
cell (10.10), then paste it to some other cell, the formula is maintained as
=R[-2]C[-2]. The formula in the cell that I just pasted into will not refer
to cell (9,9).

Is there someway of forcing Excel to update the relative references in the
formula when I do a copy?

Note that I really want to do this programmatically in a macro but I can't
seem to find a way to do this without jumping through some hoops. If there
was some way of doing it, then I could just record a macro and see how it's
done.

I'm using Excel X and Excel 2004 with the latest updates on Tiger with the
latest updates.

Thanks

Leslie

 

[JE McGimpsey]

My question concerns copying a formula in a cell that refers to another cell
using relative references, then pasting that cell and it's formula to another
cell and maintaining the correct relationships.

Suppose cell (10,10) refers to cell (9,9) using formula =R[-1]C[-1].

If I insert a column and row between cell (10.10) and cell (9.9), the
spreadsheet correctly changes the formual to =R[-2]C[-2]. But if I select
cell (10.10), then paste it to some other cell, the formula is maintained as
=R[-2]C[-2]. The formula in the cell that I just pasted into will not refer
to cell (9,9).

Is there someway of forcing Excel to update the relative references in the
formula when I do a copy?

Note that I really want to do this programmatically in a macro but I can't
seem to find a way to do this without jumping through some hoops. If there
was some way of doing it, then I could just record a macro and see how it's
done.

I'm fuzzy on what you're trying to accomplish. When you copy relative
references, the relative offsets are maintained.

It sounds like you really want to use an absolute reference:

=R9C9

The behavior will be the same when you insert a row or column, but when
you copy it, it will continue to refer to the original referenced cell...

 

[LRL]

I wanted to use the same formula in different places in the SS to do the same
thing, refer to a cell one row up one column left, without having to create
different formulas for those cells, e.g. R9C9 in one spot, r20C31 at a
different spot, etc. I generated the formula in the macro and used the same
formula R[-1]C[-1] wherever I needed it (the real formula's are more
complicated).

This worked great until the copy/paste problem created my buddy #REF. You're
right that a the absolute references do not have this problem and I will
probably have to use those types of formulas instead of the relative
references if I want to be able to duplicate the cell someplace else on the
sheet and have it work.

Thanks.

My question concerns copying a formula in a cell that refers to another cell
using relative references, then pasting that cell and it's formula to another
cell and maintaining the correct relationships.

Suppose cell (10,10) refers to cell (9,9) using formula =R[-1]C[-1].

If I insert a column and row between cell (10.10) and cell (9.9), the
spreadsheet correctly changes the formual to =R[-2]C[-2]. But if I select
cell (10.10), then paste it to some other cell, the formula is maintained as
=R[-2]C[-2]. The formula in the cell that I just pasted into will not refer
to cell (9,9).

Is there someway of forcing Excel to update the relative references in the
formula when I do a copy?

Note that I really want to do this programmatically in a macro but I can't
seem to find a way to do this without jumping through some hoops. If there
was some way of doing it, then I could just record a macro and see how it's
done.

I'm fuzzy on what you're trying to accomplish. When you copy relative
references, the relative offsets are maintained.

It sounds like you really want to use an absolute reference:

=R9C9

The behavior will be the same when you insert a row or column, but when
you copy it, it will continue to refer to the original referenced cell...

 

[JE McGimpsey]

I wanted to use the same formula in different places in the SS to do the same
thing, refer to a cell one row up one column left, without having to create
different formulas for those cells, e.g. R9C9 in one spot, r20C31 at a
different spot, etc. I generated the formula in the macro and used the same
formula R[-1]C[-1] wherever I needed it (the real formula's are more
complicated).

This will always refer to one cell up and one cell to the left of the
current cell:

=OFFSET(RC,-1,-1)

You could also use that as a defined name:

Choose Insert/Name/Define

Names in Workbook: Up1Left1
Refers to: =OFFSET(RC,-1,-1)

Click Add, then OK.

Now in your worksheet use the formula

=Up1Left1

 

[LRL]

Thanks!

I swear that I spend half of my development time rewriting my VBA code to do
the same thing a better way.

I wanted to use the same formula in different places in the SS to do the same
thing, refer to a cell one row up one column left, without having to create
different formulas for those cells, e.g. R9C9 in one spot, r20C31 at a
different spot, etc. I generated the formula in the macro and used the same
formula R[-1]C[-1] wherever I needed it (the real formula's are more
complicated).

This will always refer to one cell up and one cell to the left of the
current cell:

=OFFSET(RC,-1,-1)

You could also use that as a defined name:

Choose Insert/Name/Define

Names in Workbook: Up1Left1
Refers to: =OFFSET(RC,-1,-1)

Click Add, then OK.

Now in your worksheet use the formula

=Up1Left1