insert rows programmatically with formula change

[Newbie]

I have a spread sheet that is grouped where the top line of the group
determines the Qty of Item and the Total value for the item

eg 1 Table (row 1) requires 1 table top, 2 chairs and 4 legs (rows 2 to 4)
if I change the qty in Row 1 (i.e. No of Tables) the parts automatically
change as well.

eg
Col A B C D
E
Qty Unit
formula Rate Value
Total
Row 1 1
300
Row 2 2 A2*A$1 50 100
Row 3 1 A3*A$1 100 100
Row 4 4 a4*A$1 25 100


I want to be able to copy this grouping so that the formula in column B (the
A$1 part) always refers to the relative Row 1 of the group i.e if I copy
and insert the group at Row 6 the formula in Row 6 would change to A7 * A$6
etc

Is there a way of using a command button to copy the group and then paste it
into the spreadsheet at the row that has been selected and then update the
formula in the rows with the row number of the insertion point

eg it copies the group , I then select row 13 as the insertion point, this
row number is stored in a variable, so that the formula in row 14, 15 and 16
is updated to A14*A$13, A15*A$13 and A16*A$13

I do have some knowledge of VBA but in Access not Excel so a dummies guide
would be much appreciated to any solution that may be offered

Thanks

 

[Mike Fogleman]

It has to do with the $ sign in the formula. You used A2*A$1 in row 2 so you
could drag down the formula to row 4, and all formulas would refer to row 1
(absolute). Now when you move this section you will want to remove the $
before you copy it. That way they will reference the new rows that it is
pasted to (relative). To quickly do that select B2:B4 and hit Ctrl+H
(replace with). Replace what: $, Replace with: (leave blank), click replace
all. Now you can copy your formulas to anywhere and their relationship
remain intact.

Mike F

 

[Newbie]

Thanks for this. This would be ok if I was doing the copying. This is a
spreadsheet that is used by people that have no knowledge of excel and
therefore editing to formulas is not a solution unfortunately.
It needs to be 'fool' proof

Thanks again

 

[Mike Fogleman]

Then remove the $ signs from the formulas yourself, before you give it to
them. The $ was for your convenience when you drag down the formulas. Once
the formulas are in place the $ signs are no longer needed.

Mike F

 

[Newbie]

That is ok when copying the whole group however if they want to insert a row
within the group the formula needs to have the $ sign so that it refers to
the group header.

 

[Mike Fogleman]

Is inserting rows in the middle of a group something the user would normally
be doing?

Mike F