Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
zero's in linked cells | Excel Discussion (Misc queries) | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
quick way to copy-paste a formula linked to cells in another file | Excel Worksheet Functions | |||
Show cells have been read - linked | Excel Discussion (Misc queries) | |||
Update linked cells within a workbook??? | Links and Linking in Excel |