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

Mike F
"Newbie" wrote in message
...
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" wrote in message
. ..
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" wrote in message
...
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" wrote in message
. ..
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" wrote in message
...
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