The SHEET Function Syntax
The SHEET function is added to a cell in the form of a formula. Manually adding the function to the cell takes a few steps shown below: The syntax of the SHEET function can be seen below in bold. Followed by a detailed explanation of the functional argument. =SHEET(Value) Value - This is the name of the spreadsheet or a reference of a spreadsheet (Optional). If left blank, the spreadsheet number of the current spreadsheet will be returned.
Examples
There are four SHEET function examples in the illustration below. The first example is the SHEET function in its simplest form with no reference. Since the function is being used in the first spreadsheet (Sheet1) the result will be 1. In the second example, a name table is referenced showing that the table is located on the second spreadsheet or sheet2. In the event that you let another cell in another spreadsheet equal table1, the returned value will still only reference the spreadsheet that the table was named in. In the third example, a named range is used. The same above rule for a named table pertains to a named range. In the last example, a specific cell is referenced from a specific spreadsheet to return the spreadsheet number.
Inserting the SHEET Function
The SHEET function can be inserted into a cell by first selecting the cell, then clicking on the formulas tab. Next, click on the other functions button and the information option. Select SHEET from the list of functions.
Select the Function
Once the formula builder appears, a value representing a spreadsheet name, a named table, or a named range can be added to the sole argument field. The result will display at the bottom of the formula builder side bar. This will enable you to test the function for errors. After the argument is added, the done button can be clicked for the function to be entered.
Enter the Argument in the Formula Builder
Additional Considerations
Spreadsheets included - When used, this function takes into account all spreadsheets that are included in the workbook. Even the hidden spreadsheets are counted and will have a location based on the spreadsheet’s current location in the backend of the software. Errors - When argument values have errors the SHEET function will return a #REF error. If a sheet name is used and the spreadsheet cannot be referenced an #NA error will appear. Order - As the order of spreadsheets changes, so will the results of the SHEET functions. This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional. © 2022 Joshua Crowder
