Examples of using the SHEET and SHEETS functions in Excel formulas

SHEET function in Excel returns a numeric value corresponding to the sheet number referenced by the link passed to the function as a parameter.



SHEET and SHEETS functions in Excel: description of arguments and syntax

SHEETS function in Excel returns a numeric value that corresponds to the number of sheets referenced.

Notes:

  1. Both functions are useful for use in documents containing a large number of sheets.
  2. A sheet in Excel is a table of all the cells that are displayed on the screen and are outside of it (a total of 1,048,576 rows and 16,384 columns). When sending a sheet to print, it can be divided into several pages. Therefore, the terms “sheet” and “page” should not be confused.
  3. The number of sheets in the book is limited only by the amount of PC RAM.

Function has only 1 argument in its syntax and is optional for: =SHEET(value).

  • value is an optional function argument that contains text data with the name of the sheet or a link for which you want to set the sheet number. If this parameter is not specified, the function will return the number of the sheet in one of the cells of which it was written.

Notes:

  1. When the sheet works, all sheets that are visible, hidden and very hidden are taken into account. Exceptions are dialogs, macros, and diagrams.
  2. If the function argument is a text value that does not match the name of any of the sheets contained in the book, the error #NA will be returned.
  3. If an invalid value was passed as an argument to the function, the result of its calculation will be the error #REF!.
  4. Within the object model (the hierarchy of objects in VBA, in which Application is the main object and Workbook, WorkSheet, etc., are child objects), the SHEET function is not available because it contains a similar function.

The function has the following syntax: =SHEETS(reference).

  • reference - an object of reference type for which you want to determine the number of sheets. This argument is optional. If this parameter is not specified, the function will return the number of sheets contained in the book where it was written.

Notes:

  1. This function counts the number of all hidden, very hidden and visible sheets, with the exception of charts, macros and dialogs.
  2. If an invalid reference was passed as a parameter, the result of the calculation is the error code #REF!.
  3. This function is not available in the object model due to the presence of a similar function there.


How to get sheet name by formula in Excel

Example 1. In carrying out the calculated work the student used the Excel program, in which he created a book of several sheets. For his own convenience, the student decided to display in the cells A2 and B2 of each sheet data on the name of the sheet and its serial number, respectively. For this, he used the following formulas:

Example 1.

Description of arguments for the MID function:

  1. =CELL("filename") is a function that returns text in which the MID function searches for a specified number of characters. In this case, the value “C:\Users\soulp\Desktop\[Workbook.xlsx] Static_Calculations” will return, where after the “]” symbol is the required text - the name.
  2. =FIND("]",CELL("filename"))+1 is a function that returns the position number of the character "]", one is added so that the MID function does not take into account the character].
  3. 31 - the maximum number of characters in the name.

=SHEET() - this function without parameter returns the number of the current sheet. As a result of its calculation, we get the number of sheets in the current book.

Examples of using the SHEET function and SHEETS

Example 2. The Excel workbook contains several sheets. It is necessary:

  1. Return the current sheet number.
  2. Return the sheet number with the name "Static_calculations".
  3. Return the number of the sheet "Dynamic_calculations", if its cell A3 contains the value 0.

Enter the data in the table:

Example 2.

Next, we write the formulas for all 4 conditions:

  • For condition # 1, use the following formula: =SHEET()
  • For condition # 2, enter the formula: =SHEET("Static_calculations")
  • For condition number 3 we write the formula:

The IF function checks whether the value stored in the A3 cell of the Dynamic_ calculations is equal to zero or null.

As a result, we get:

.

Processing information about the sheets of the book on the formula Excel

Example 3. To determine the contains several sheets. It is necessary to determine the total number of sheets, as well as the number contained between the “Static_calculations” and “Economic_calculations”.

The source table is:

.

The total number of sheets is calculated by the formula:

To determine the number contained between these two sheets, we write the formula:

  1. Static_calculations: Economic_calculations!A2 - A reference to cell A2 of the range of sheets between "Static_calculations" and "Economic_calculations" including these sheets.
  2. To get the desired value, 2 was subtracted.

As a result, we get the following:

.

Download examples SHEET and SHEETS functions in Excel formulas

The formula displays detailed information on the data on the sheets in a certain range of their location in the Excel workbook.


en ru