# INDIRECT Function to Get Values from Different Sheet in Excel

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 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.  • After that, complete the table similarly. ### 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. ### 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. ## 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.

## Related Articles #### Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

1. Reply 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..

2. Reply 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!

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

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

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

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

5. Reply 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.

• Reply 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! 