While working in **Excel**, there are many cases where you want to refer your worksheet to a different worksheet. The topic that we will be discussing in this article is how to use **Excel INDIRECT** function on a different sheet where we will see the process of how the **INDIRECT function** can be used to refer to a worksheet in another sheet.

## Download Practice Workbook

Download this workbook and practice while going through the article.

## 3 Examples of Using INDIRECT Function to Get Values from Different Sheets

Now, I will show 3 examples of using **the INDIRECT function**. My first one will be the use of the **INDIRECT** function to refer to a worksheet. Then, I will show how to perform some numeric calculations using this function followed by applying **data validation** using the **INDIRECT** function. Let’s see them one by one.

### 1. Use INDIRECT Function to Refer to Worksheet

This is the dataset for this example. There are 3 sheets named **S1**, **S2,** and **S3**. I will refer **S1** and **S3** sheets and get the values of specific cells from these sheets.** **

**Steps:**

- Go to
**D5**and write down the following formula

`=INDIRECT("'" & B5 & "'!" & C5)`

**Formula Explanation:**

- The
**INDIRECT**function takes the reference from cell**B5**where**S1**is written. - A set of double quotes is used before
**A2**, indicating the text string. - For combining the arguments, “
**&**” is used. - For separating a worksheet from a cell “
**!**” sign is used. Here using “**!**” with**“”**we are referring to sheet**S1**. - For preventing errors, a single quote is placed inside the double quotes.
- In the end,
**C5**is written which specifies the location of the cell. After the formula is placed in cell**D5**you will get the result**19**as the cell value of**C13**in Sheet**S1**is**19**.

- Press
**ENTER**to get the output.

- For your convenience, I have shown the formula used in
**D5**using**the FORMULATEXT function**.

- After that, complete the table similarly.

**Read More:** **INDIRECT Function with Sheet Name in Excel (4 Criteria)**

### 2. Perform Numeric Calculation from Different Worksheets Using INDIRECT Function

I will show how you can perform calculations from different sheets using the **INDIRECT function**.

**Steps:**

- Go to
**C5**and write down the following formula

`=SUM(INDIRECT("'"&B5&"'!"&"B5:B8"))`

**Formula Explanation:**

- Here the formula indicates the sheet
**S1**as**S1**is written in cell**B5**. - The defined range here is
**B5:B8**. So, the formula will select the range**B5:B8**and calculate the sum of the values placed in those cells.

- Then, press
**ENTER**to get the output.

- Finally, complete the rest of the table.

**Read More:** **How to Use Excel INDIRECT Range (8 Easiest Ways)**

### 3. Use INDIRECT Function with Data Validation Feature

You can also use the **INDIRECT** function in the **Data Validation** feature. To show this example, I will use another dataset.

There are sales data for 2015,2016 and 2017. I will calculate the total and **average sales** using **data validation** and the **INDIRECT** function.

**Steps:**

- First of all, select
**C4**. - Then, go to the
**Data** - After that, select the icon for
**Data Validation**.

**Data Validation**window will appear.- Allow
**List**from the**drop-down**. - Then, write
**2015**,**2016**, and**2017**as your source. - After that, click
**OK**.

**Excel**will add a data validation feature.

- Then, go to
**C6**and write down the formula.

`=SUM(INDIRECT("'"&C4&"'!"&"C5:C16"))`

- Then, press
**ENTER**to get the output.

- Similarly, get the average sales.

- Now, if you change the year, the values will change.

**Read More: ****Create Drop-Down List Using INDIRECT Function in Excel**

## Things to Remember

- For referring to another sheet, the worksheet needs to be opened.
- The result of the
**INDIRECT**function is evaluated in real-time. - The cell_value is an optional argument and is taken as
**Boolean**If it is a**TRUE**value, then the reference style will look like the**“=A1”**style. - If the cell_value is a
**FALSE**result, then it will look like**R1C1.**

## Conclusion

As you can see it is quite easy to use the **INDIRECT** function in Excel different sheet. We can even perform many numeric calculations. Hope this article will find your interest. Don`t hesitate to ask any questions.

Thank you for sharing.

Why it cannot work between different workbooks if is closed?

Is there any work around to get a data from a workbook even if it is closed??

Thank you again..

Thank you very much. This article shows how you can pull data from different worksheets present in a workbook. However, if you want to extract data from sheets present in different workbooks, kindly go through the article linked below. It will guide you through the complete procedure.

https://www.exceldemy.com/excel-macro-extract-data-from-multiple-excel-files/

Good luck.

Thank You for the exact answer I spent a lot of time looking for. I found many answers on the web but nothing like the simple one you gave that works for exactly what I was looking for. Thanks again!

You’re most welcome. Glad to know that it helped you.

Thank you very much for this write-up. It saved me as well!

We’re very glad to hear that we could be of help to you as well.

Good luck.

Hi, what could be wrong in my formula as this gives me #REF! error.

=COUNTIFS(INDIRECT(“‘Attendance!”&(ADDRESS(MATCH($C2,Attendance!3:3,0),11,4,1)&”:”&ADDRESS(MATCH($C2,Attendance!3:3,0),40,4,1))),”P”)

Hello,

Without seeing the worksheet, it is tough to say. Can you share the file or at least a screenshot of the data?

Thanks.

Hi, when using INDIRECT when the source cell has a hyperlink, how do I bring across the cell contents including the hyperlink. My result cell has the cell contents but the hyperlink is not present.

Hi GEOFFREY! Your problem is not quite clear to us. However, if you simply copy and paste the cell contents, the hyperlinks will also be pasted. If this is not what you were expecting, then we would request you to elaborate on your issue. You can also send your Excel file to us through email. Thank you!

I’m sorry but I may not be in the right place or I’m not fully understanding your process.

From what I gather, what you’re doing is accessing information via different workSHEETS. For me, that seems fairly straight forward, but my problem that I’m looking for that is not represented here is gathering said information from a different workBOOK. Again, I may be in the wrong place, but maybe you could point me in the right place to handle my problem. Thanks!

Thank you very much. This article shows how you can pull data from different worksheets present in a workbook. However, if you want to extract data from sheets present in different workbooks, kindly go through the article linked below. It will guide you through the complete procedure.

https://www.exceldemy.com/excel-macro-extract-data-from-multiple-excel-files/

Moreover, you can create a simple formula:

=’D:\SOFTEKO\[task_problems.xlsx]Sheet1′!$C$18Where D is the drive location, SOFTEKO is the folder name, task_problems is the desired excel file, Sheet1 is the worksheet, and C18 is the required cell value.

Here, make changes according to your requirements.

Good luck.