formula

[Newbie]

Excel 2003

I have a spreadsheet that has groupings
col a b c d
row 1 2 sum(r2:r5)
row 2 1 =a2*a1
row 3 2 =a3*a1


If I want to copy the whole group -with the formula in Column B I can copy
the whole group (Row 1 to Row 3) and Insert Copied Cells and all is fine.

If I want to insert rows into the group then the column B formula changes to
a3*a2 (for eg)

If I change the formula in column b to a2*$a$1 I can insert rows within the
group ok but if I copy the whole group the new group is still looking at
cell A1

How can I overcome this problem - is there a way of perhaps naming the top
row of the group so that when the group is copied the formula always looks
at the top line of the group?

Thanks

 

[Keith74]

Try inserting a dollar symbol "$" in front of the row number in your
formula.

hth

keith

 

[Newbie]

Thanks but that doesn't work when I start a new group becuase it will still
be looking at the original row1

 

[Keith74]

Hi

Now i've worked out the problem, i'm afraid i dont know a way to do
what you're asking using formulas. Hopefully someone will correct me
but i dont think theres a way to preserve relative formula positions
in copies and inserts in that way

 

[Newbie]

Thanks anyway