Instruction
1
In the simplest case, if the formula uses data from one workbook, when you insert a function in the entry field values record the coordinates of a fixed cell in the format $A$1. For example, you need to sum the values in column B1:B10 with the value in cell A3. Then in the function bar, write down the formula in the following format:
=SUM($A$3;B1).
Now when the autocomplete will only change the name string of the second term.
=SUM($A$3;B1).
Now when the autocomplete will only change the name string of the second term.
2
Similarly, you can sum data from two different books. Then in the formula we need to specify the full path to the cell in the closed workbook in the following format:
=SUM($A$3;'Drivename:\Catalogproducts\Username\FolderName\[Имя_файла.xls]Sheet1'!A1).
If the second book (called the source) is open and files are in one folder, then in the final book, specify only the path from a file:
=SUM($A$3;[Имя_файла.xls]Sheet1!A1).
=SUM($A$3;'Drivename:\Catalogproducts\Username\FolderName\[Имя_файла.xls]Sheet1'!A1).
If the second book (called the source) is open and files are in one folder, then in the final book, specify only the path from a file:
=SUM($A$3;[Имя_файла.xls]Sheet1!A1).
3
However, this record if you are going to add or remove rows/columns in the original workbook before the first cell range you want to end the book values in the formula will change. When you insert blank rows above the starting cell, instead of the second term in the formula of the final book will be zeros. To avoid this, the workbook you want to link together.
4
To this end the book will have to add the link column. Open the source workbook, and then select a cell whose value should be recorded regardless of the table operations. Copy this value to clipboard. Go to the sheet in the destination workbook that will contain the formula.
5
In the menu "edit" select "paste Special" and in the opened window click the "Paste link". By default, the cell will be written the expression in the format:
=[Книга2.xls]Sheet1!$And$1.
However, this expression is displayed only in the formula bar and the cell is entered, its value. If you need to bind the end of the book with the next variation from the original, remove the $ sign from the formula.
=[Книга2.xls]Sheet1!$And$1.
However, this expression is displayed only in the formula bar and the cell is entered, its value. If you need to bind the end of the book with the next variation from the original, remove the $ sign from the formula.
6
Now in the next column, insert a summation formula in the usual format:
=SUM($A$1;B1),
where $A$1 – address fixed-cell in the final book.
B1 is the cell address containing the formula with the beginning of the variation of a number of other books.
=SUM($A$1;B1),
where $A$1 – address fixed-cell in the final book.
B1 is the cell address containing the formula with the beginning of the variation of a number of other books.
7
In this way the formula is written, the value B1 of the original table will remain unchanged no matter how many lines you added above. If you change it manually, the calculation result by the formula in the destination table will change too.