Ms Excel: Sheets : Free Online Spreadsheets For Personal Use

The SHEET and SHEETS functions were added in Excel 2013. The SHEETS function counts all the sheets in a reference, and the SHEET function returns the sheet number for a reference.

Đang xem: Ms excel: sheets

Introduction to SHEET and SHEETS Functions

Among the new functions in Excel 2013 are SHEET and SHEETS.

SHEETS function counts all the sheets in a reference SHEET function returns the sheet number for a reference

To see how you can use the new SHEET and SHEETS functions in Excel 2013, please watch this short video tutorial.

Or watch on YouTube

Using the Functions

You can use the SHEET and SHEETS formulas to do some troubleshooting in a workbook.

SHEETS Check for missing values Notice that sheets are hidden SHEET Show sheet number in list of sheets, to spot missing numbers Calculate sheet number at top of each sheet, to identify gaps for hidden sheets

SHEETS Function

Get the Number of Sheets

The SHEETS function tells you how many sheets are in a reference. In the screen shot below, this formula:

=SHEETS(Dept01:Dept03!$I$4)

will return 3, as the number of sheets in that 3D reference.

Check for Missing Values

In the formula above, the SHEETS function told us how many sheets were in the reference. We could use the COUNT or COUNTA function to see if there is a value in each sheet.

=COUNTA(Dept01:Dept03!$I$4)

Then, subtract the count from the number of sheets, to see if there are any missing values.

In the worksheet shown above, there are 3 sheets in the reference, but only 2 entries. You can check the sheets, to find the missing value.

Notice Hidden Sheets

In the SHEETS function, if you omit the reference, it tells you how many sheets are in the workbook — visible, hidden or very hidden sheets.

=SHEETS()

In the screen shot below, the result is 8, but you can only see 4 sheet tabs.

If the SHEETS result isn”t the same as the number of visible tabs, you”ll know that it”s time to look for hidden sheets.

Xem thêm: Đồ Án Điện Tử Công Suất Tia 2 Pha, Tailieuxanh

SHEETS Limitations

Unfortunately, the SHEET function doesn”t work with multiple references, such as:

=SHEETS(Sheet1!B3,Sheet2!B3,Sheet4!B3)

And, you can”t use INDIRECT to create the reference, based on sheet names in a cell, because INDIRECT doesn”t support 3D references.

SHEET Function

Check the Sheet Number

The SHEET function tells you the sheet number for a reference. In the screen shot below, this formula:

=SHEET(Dept02!I6)

returns a 3, as the number for the Dept02 sheet.

It”s the 3rd sheet tab, so you can tell that none of the sheets before it are hidden.

The SHEET function can also return the sheet number for a named range or a named Excel table

See the Active Sheet Number

In the SHEET function, if you omit the reference, it tells you the number of the active sheet.

=SHEET()

In the screen shot below, the result is 5, but the 3rd sheet tab is active. That indicates there are 2 hidden sheets before the active sheet.

Show Sheet Numbers in a List

The SHEET function takes a regular reference — not a 3D reference like the SHEETS function. So, you can use INDIRECT with SHEET, to show the sheet numbers in a list of sheet names.

The screen shot below shows a menu sheet, with all the sheet names in the workbook. In the adjacent column, this formula calculates the sheet name.

=SHEET(INDIRECT(“”” & $B3 & “”!A1″))

Now, I can sort the list in alphabetical order, or numerical order, and check for hidden sheets.

Xem thêm: cách viết tóm tắt đồ án tốt nghiệp

SHEET Limitations

The SHEET function as promised, but it would be nice to have a function that lets you get information about a sheet, based on its number. Or, perhaps we could use a sheet number to build a reference, just as we can use the row number and column number.

That way, we could refer to the sheet that is -1 or +1 away from the active sheet.

hướng dẫn the Sample File

hướng dẫn the zipped sample SHEET and SHEETS Functions file. The workbook also uses the FORMULATEXT function, to show the formulas in the example cells. These functions only work in Excel 2013 or later, so you”ll see errors if you open the file in an earlier version.