Keeping Cells linked
 

Hi,
I have two worksheets containing data with each column totalled seperately
in the last row, also, each sheets totals are linked to the other sheet so
that the two totals can then be added together. I regularly have to add or
delete a row or rows from one or the other sheet which then throws the link
out. Is there a way I can make each sheet total after removing/adding rows
without having to change the link formula each time?
Thx
Kev

Keeping Cells linked
 

If there's nothing in the columns you're totalling except the data and a
non-numeric header, you could use sum formulas on the totals sheet. That is,
instead of =Detail!C40, use =sum(Detail!C:C)/2 (divide by two to account for
the fact that the total is at the bottom of each detail column).
Another way would be to add a blank cell below each column of figures,
include the blank row in the sum, and always insert a cell before the blank
cell. If your data is in C2:C39, calculate your sum in C41 as =sum(c2:c40).
To add another entry, click in C40 then insert cells (shift cells down).
That will adjust your sum function's cell reference to c2:c41, and should
also adjust the link to point to c42.

"kevhatch" wrote:

Hi,
I have two worksheets containing data with each column totalled seperately
in the last row, also, each sheets totals are linked to the other sheet so
that the two totals can then be added together. I regularly have to add or
delete a row or rows from one or the other sheet which then throws the link
out. Is there a way I can make each sheet total after removing/adding rows
without having to change the link formula each time?
Thx
Kev

Keeping Cells linked
 

Hi hevhatch,
you are experiencing a common user crisis wherein after building the data
file in worksheet(s) and been linked together...maintaining it becomes a user
problem..

bpeltzer's suggestion is a good one...
try also another ways
1. open all the linked workbooks when you are doing updates on your data,
dont delete the row containing the sum formula. Before making changes, try to
click the Auditing toolbar "trace dependents" to see if your data are being
referred by which workbooks...also visit EditLinks to see the workbooks that
are dependent or been utilized by the current open workbook(s)...
2. try to put the column sum on the top row(s) eg. ROW(1) in this case u
don't need to open all the linked files, altogether. e.g. A1=sum(A10:A20000)
3. It will be better to put all your sheets under one workbook to avoid the
above 2 suggestions.
regards
--
*****
birds of the same feather flock together..



"kevhatch" wrote:

Hi,
I have two worksheets containing data with each column totalled seperately
in the last row, also, each sheets totals are linked to the other sheet so
that the two totals can then be added together. I regularly have to add or
delete a row or rows from one or the other sheet which then throws the link
out. Is there a way I can make each sheet total after removing/adding rows
without having to change the link formula each time?
Thx
Kev