Siam Hasan Khan
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$18
Where 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.