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 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)

Using INDIRECT function to refer worksheets

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.

Using INDIRECT function to refer worksheets

  • 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"))

Numeric calculation Using INDIRECT function

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.

Numeric calculation Using INDIRECT function


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.

Data Validation and INDIRECT function from different worksheet

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.

Data Validation and INDIRECT function from different worksheet

  • Excel will add a data validation feature.

  • Then, go to C6 and write down the formula.
=SUM(INDIRECT("'"&C4&"'!"&"C5:C16"))

Data Validation and INDIRECT function from different worksheet

  • Then, press ENTER to get the output.

  • Similarly, get the average sales.

Data Validation and INDIRECT function from different worksheet

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

Data Validation and INDIRECT function from different worksheet


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

  1. Excel Reference Cell in Another Sheet Dynamically
  2. How to reference a cell from a different worksheet in Excel
  3. How to use cell references with multiple worksheets in Excel
Siam Hasan Khan

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.

8 Comments
  1. 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. 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!

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

  4. 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”)

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

Leave a reply

ExcelDemy
Logo