Llink to a cell in another Excel sheet

In all previous lessons, formulas and functions were referenced within one sheet. Now we will just a little extend to the possibilities of their links.

The Excel allows you to make links in formulas and functions to other worksheets and even books. You can link to the data of the separate file. By the way, in this way, you can restore to a data from a damaged xlsx file.



The link to the sheet in the Excel formula

You need to enter incomes for January, February and March on three separate sheets. Then on the fourth sheet in the cell B2 to sum them.

need to enter incomes.

There is the question arises: how to link to another sheet in Excel? To implement this task, do the following:

  1. To fill in Sheet1 (January), Sheet2 (February) and Sheet3 (March) as shown in the figure above.
  2. To go to Sheet4 in the cell B2.
  3. To put the «=» sign and go out to the Sheet1 (January). There you need to click by the left mouse button on the cell B2.
    4. Sheet1 January B2.
  4. To put the «+» sign and repeat the same steps of the previous paragraph, but only on the Sheet2 (February), and then on the Sheet3 (March).
    5. repeat the same steps.
  5. When the formula has the following form: =January!B2+February!B2+March!B2, you should to press Enter. The result should be the same as in the figure.


How can I do the link to a sheet in Excel?

The link to the sheet is slightly different from the traditional link. It consists of 3 elements:

  1. The name of sheet.
  2. The exclamation mark (the serves as a separator and helps visually determine what sheet belongs to the cell address).
  3. The address is in the cell in the same sheet.

The note. The links to the sheets can be entered and manually that will work the same. Just in the above case is less likely to allow a syntactic error, because of which the formula will not work.

The link to a sheet in another Excel workbook

The link to a sheet in another book has already 5 elements. It looks like this: ='C:\Docs\[Report.xlsx]Sheet1'!B2.

The description of elements of the link to another Excel workbook:

  1. The path to the book file (after the sign is the apostrophe is opened).
  2. The name of the book file (the name`s file is in square brackets).
  3. The name`s sheet of this book (after the name the apostrophe closes).
  4. The exclamation mark.
  5. The reference to the cell or the cells` range.

This link should read as follows:

  • the book is located on the disk C: \ in the Docs folder;
  • the file`s name of the «Report» book with the extension «.xlsx»;
  • on the «Sheet1» in the cell B2 is the value to which the formula or function refers.

The helpful advice. If the book file is damaged, but you need to get the data out of it, you can manually register the path to the cells with relative links and to copy them to the entire sheet of the new workbook. It works in 90% of cases.

Without functions and formulas, Excel would be one large table that designed to manually fill in data. Thanks to functions and formulas, it is a powerful computational tool. And the results obtained are dynamically represented in the desired form (if necessary even in a graphical one).

All Classes

en ru