EXCEL FUNCTIONS-SHEET

CONTENTS

INTRODUCTION

SHEET function comes under the INFORMATION category in Excel.

THE SHEET function simply returns the sheet number of any reference given by the SHEET NAME, ANY DEFINED NAME, ANY TABLE NAME ETC.

The SHEET FUNCTION is very useful if we need to find out any reference for any table , any specific range or cell defined by name in any of the sheet. But its important to decide the accessibility of the defined name or range to be workbook so that the function can access it from other sheet.

We’ll discuss all the points in the following article.

PURPOSE OF SHEET FUNCTION IN EXCEL

SHEET FUNCTION returns the sheet number of the reference which may be the NAME OF THE SHEET, ANY DEFINED NAME OF A CELL OR RANGE OF CELLS OR ANY TABLE etc.

PREREQUISITES TO LEARN SHEET FUNCTION

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

  •  Basic understanding of how to use a formula or function.
  •  Basic understanding of rows and columns in Excel.
  • Some information about the financial terms is an advantage for the use of such formulas.
  •  Of course, Excel software.

Helpful links for the prerequisites mentioned above

What Excel does? How to use formula in Excel?

SYNTAX: SHEET FUNCTION

The Formula Syntax is the way any function is used.

The Syntax for the function is

=SHEET(REFERENCE/VALUE)

REFERENCE/VALUE It contains any value such as NAME OF THE SHEET in the  “” or any defined name or Table name etc.



EXAMPLE: SHEET FUNCTION IN EXCEL

DATA SAMPLE

For testing the SHEET FUNCTION let us create different entries on the different sheets.
Look at the animated picture below for once, and read the text given below.

IN SHEET 2, we’ll create a table with the table name GYANKOSH_TABLE.

In SHEET 3, we’ll create a cell range named WEEKDAYS.

We’ll name the SHEET 4 as “special cells”

and try to find the sheet number of all these elements from the first sheet of the workbook.

EXCEL:SHEET FUNCTION ANIMATED EXAMPLE

STEPS TO USE SHEET FUNCTION

FOLLOW THE STEPS TO USE SHEET FUNCTION

  • Select the function where you want the result.
  • Enter the formula as
  • =SHEET(REFERENCE/VALUE).

For our example, we’ll use the formula as

The formula used for various usages of SHEET FUNCTION are given below.

RESULTS

RESULTFORMULA USED
FIND THE INDEX OF SHEET “special cells”4=SHEET(“special cells”)
FIND THE INDEX OF SHEET CONTAINING CELL RANGE NAMED “WEEKDAYS”3=SHEET(WEEKDAYS)
FIND THE INDEX OF SHEET CONTAINING TABLE NAMED “GYANKOSH_TABLE”2=SHEET(GYANKOSH_TABLE)

The result can be seen as the SHEET NUMBER for the reference given.

Leave a Reply

Your email address will not be published. Required fields are marked *