Gmats,
If I understand your question correctly, you have a table in Excel of which
you
have a total (sum) which you want to show up in other worksheets and/or
other
workbooks. This is done by linking to the cell containing the total. When
you insert
a row in your table, your formula does not adapt to the new table-size and
the links
to the total value are not really broken, but do not show the total, but a
value in one
of the cells above the total (depending on the number of rows inserted).
The not-adapting formula is probably caused by inserting the row BEFORE row
46
(I assume the row of the total). You could solve this by keeping one empty
row in
between the table and the total, which is included in your formula, and
above which
you insert your new lines. For example: You have 45 lines of data, line 46
is empty,
line 47 contains your totals. The formula in line 47 is =Sum(A1:A46). Now
you have
your macro insert lines before line 46 again, but since line 46 itself is
within the range
of your totalisation formula the range covered by your formula will expand.
This is one
part of the solution.
The other one is the broken links to the total value. This problem should
not occur to
links from other worksheets within the same workbook, but might occur to
links from
other workbooks if the linking workbook was not open at the time of the
insertion of
the new row. You could assign a range name to the cell containing the total,
so the
links will stay valid, since the cell the range name is referring to is
updated upon insertion
of a new row. (How to insert a rangename: select the range you want to
assign the name
to, select Insert / Name / Define and type the name in the top field in the
pop-up window.
Click Ok).
Kind regards,
Marcel Kreijne
Quandan - Steunpunt voor spreadsheetgebruikers
(Quandan - dutch supportsite for spreadsheetusers)
www.quandan.nl
You could solve this in a couple
