Function SHEETS and formulas for working with other sheets in Excel

SHEET function is designed to return the number of a specific sheet with a space, which allows access to the entire workbook in MS Excel. SHEETS function provides the user with information on the number of sheets contained in the workbook.

Formulas using links to other Excel sheets

Suppose we have a company DecArt in which employees work and their monthly, salary is calculated. This company has information about the average monthly salary in Excel, and the data on it are placed on different sheets: on sheet 1 there are data on wages, on sheet 2, the percentage bonus. We need to calculate the size of the premium in dollars, while these data were placed on the second sheet.

To begin, consider an example of working with sheets in Excel formulas. Example 1:

Let's create on sheet 1 of the workbook of the spreadsheet Excel processor table, as shown in the figure. Information about the average monthly wage:

Next, on sheet 2 of the workbook, we will prepare an area for placing our result - the size of our premium in dollars, as shown in the figure:

Next, we will need to enter into the B2 cell the formula shown in the figure below:

This formula was entered as follows: first, in cell B2, we set the "=" sign, then clicked on "Sheet1" in the lower left corner of the workbook and went to cell C3 on sheet 1, then entered the multiplication operation and switched again to "Sheet2 "to add a percentage.

Thus, when calculating the bonus of each employee, we obtained the initial data on one sheet, and the calculation was made on another sheet. This formula will be very useful when working with longer data sets in large organizations.

SHEETS function to count the number of sheets in a workbook

Consider now the example of the function. It often happens that there are too many sheets in an Excel workbook. It is not possible to visually find out their exact number; it is for this purpose that the SHEETS function has been created.

In this function, only 1 argument - "Link" and even then optional. If it is not filled, then the function returns the total number of sheets created in the current workbook of the Excel file. If necessary, you can fill in the argument. To do this, it is necessary to specify a link to the workbook, in which you need to calculate the total number of sheets created in it.

Example 2. Suppose we have a firm for the production of upholstered furniture, and it has a lot of documents that are contained in the Excel workbook. We need to calculate the exact number of these documents, since each of them has its own name, then it will take time to visually calculate their number.

The figure below shows the approximate number:

To organize the counting of all sheets, you must use function. Simply set the equal sign “=” and enter the function without filling its arguments in brackets. The call to this function is shown below:

As a result, we obtain the following value: 12 sheets.

Thus, we learned that our company has 12 documents contained in an Excel workbook. This simple example clearly illustrates the operation of the function. This feature can be useful for managers, office workers, sales managers.

Links to other sheets in document templates

Example 3. There are data on the cost of a banquet company engaged in on-site service. It is necessary to calculate the total cost of the banquet, as well as the total yield of portions of dishes, and calculate the total number of sheets in the document.

Create a table "Banquet menu", a general view of which is shown in the figure below:

Similarly, create tables on different sheets "Total cost" and "Total output":

Using the formula with links to other sheets, we will calculate the total cost of the banquet menu:

Let's go to the sheet “Total yield” and by multiplying the cells of the weight of one portion, located on sheet 2 and the total number, located on sheet 1, we will calculate the total output:

Download examples working with other sheets in Excel

As a result, we got the simplest template for calculating the cost of 1 banquet.