Formulas in MS Excel are "sliding" by default. This means, for example, that when cells are auto-filled by column in the formula, the row name will automatically change. The same happens with the column name when autocomplete the row. To avoid this, just put the $ sign in the formula before both coordinates of the cell. However, when working with this program, more complex tasks are often posed.
Instructions
Step 1
In the simplest case, if the formula uses data from one workbook, when inserting the function into the value input field, write down the coordinates of the 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 line, write the formula in the following format:
= SUM ($ A $ 3; B1).
Now, during autocomplete, only the row name of the second addend will change.
Step 2
In a similar way, you can sum data from two different books. Then in the formula you will need to specify the full path to the cell of the closed book in the format:
= SUM ($ A $ 3; 'Drive_Name: / User_Dir / User_Name / Folder_Name [File_Name.xls] Sheet1'! A1).
If the second book (called the source book) is open and the files are in the same folder, then only the path from the file is specified in the target book:
= SUM ($ A $ 3; [FileName.xls] Sheet1! A1).
Step 3
However, with this notation, if you are going to add or remove rows / columns in the source workbook before the first cell of the desired range, the values in the formula will change in the destination workbook. When inserting blank lines above the source cell, zeros will appear instead of the second term in the final book formula. To prevent this from happening, books need to be linked together.
Step 4
To do this, you will need to add a link column to the target workbook. Open the original workbook and select the cell in it, the value of which should be fixed, regardless of the operations with the table. Copy this value to the clipboard. Go to the sheet in the destination workbook that will contain the formula.
Step 5
In the "Edit" menu, select "Paste Special" and in the window that opens, click the "Insert Link" button. By default, an expression will be entered in the cell in the format:
= [Book2.xls] Sheet1! $ A $ 1.
However, this expression will be displayed only in the formula bar, and its value will be written in the cell itself. If you need to link the final book with a variation series from the original, remove the $ sign from the specified formula.
Step 6
Now, in the next column, paste the summation formula in normal format:
= SUM ($ A $ 1; B1), where $ A $ 1 is the address of a fixed cell in the target book;
B1 is the address of the cell containing the connection formula with the beginning of the variation series of another book.
Step 7
With this method of writing the formula, the value B1 of the original table will remain unchanged, no matter how many rows you add above. If you change it manually, the result of the formula calculation in the final table also changes.
