In Microsoft Excel, the INDIRECT function is generally used to return the reference specified by a text string. In this article, you’ll learn how you can use this INDIRECT function by referring to the name of an Excel sheet and returning data accordingly from that worksheet.
INDIRECT Function with Sheet Name in Excel: 4 Practical Uses
1. INDIRECT Function with Sheet Name to Refer Another Worksheet
As the INDIRECT function locks a cell reference or a range of cells, we can use this function to copy a cell or a range of cells from another worksheet. For example, in the picture below, a random chart containing the names of donors and the donation amounts of Day 1 is present in Sheet1.
Similarly, Sheet2 represents another chart for Day 2.
The following table is present in Sheet3 and in this sheet, we’ll extract the data from the previous two sheets by using the INDIRECT function. Under the ‘Day 1’ header in Column C, we’ll show the donation amounts from Sheet1 only in a vertical array.
In the output Cell C6, the required formula with the INDIRECT function will be:
=INDIRECT(C4&"!C5:C9")After pressing Enter, the formula will return the donation amounts in an array from Sheet1 at once.
In this formula, the INDIRECT function uses the cell reference C4 as an input value which contains the sheet name of the first sheet. With the use of Ampersand (&), the other necessary symbols have been used to refer to a particular range of cells from another worksheet.
Similarly, to get an array of donation amounts in Column D, we have to use the following formula in Cell D6:
=INDIRECT(D4&"!C5:C9")
Read More: How to Use Excel INDIRECT Range
2. INDIRECT Function to Refer Sheet Name from Another Workbook in Excel
We can also use and copy cell references from another workbook by using the INDIRECT function. The screenshot below represents a workbook named Donation Data that is open with Day 1 spreadsheet. We’ll extract the value of Cell C7 to another workbook with the INDIRECT function. Please keep in mind you must keep the reference workbook open while extracting data from that workbook.
📌 Step 1:
➤ Let’s open another new workbook first.
➤ In Cell B4, type ‘=’ and click on the Cell C7 of the Donation Data workbook.
📌 Step 2:
➤ Press Enter and you’ll be shown the value of $1200.
➤ Keep this Cell B4 in text format, so remove the Equal(=) symbol from the cell by editing.
Our objective is to create this formula by using the INDIRECT function.
➤ Now in Cells C6 and C7, type the names of the Excel workbook and the spreadsheet respectively from where our data will be extracted.
📌 Step 3:
➤ In the output Cell C9, type:
=INDIRECT("'["&C6&".xlsx]"&C7&"'!$C$7")➤ Press Enter and you’ll be shown the extracted data at once from the defined workbook.
3. Numeric Calculation with INDIRECT Function While Referring to a Sheet Name
By using any other function before the INDIRECT function, we can make numerical calculations for a range of data that have been previously stored by the INDIRECT function.
Let’s have a look at our initial dataset again. We’ll now use these data and make a sum for all donation amounts on a particular day.
We can consider the following output table in another worksheet in the same workbook. In Cell C6, we’ll calculate the total donation amount for Day 1 by using the SUM function before the INDIRECT function.
So, the required formula in the output Cell C6 will look like this:
=SUM(INDIRECT(C4&"!C5:C9"))After pressing Enter, the formula will return the expected total at once.
4. Use of Drop Down to Input Sheet Name in the INDIRECT Function in Excel
In this section, we’ll make a drop-down list for the sheet names in Cell C4. The range of cells (C5:C9) will show the donation amounts from the selected sheet and we have to assign these sheet names in the drop-down list. So, let’s make the drop-down list first in Cell C4.
📌 Step 1:
➤ Select Cell C4.
➤ Under the Data ribbon, choose the Data Validation command from the Data Tools drop-down.
A dialogue box will open up.
📌 Step 2:
➤ In the Allow box, choose List from the options.
➤ Enable editing in the Source box and select the range of cells (E6:E7) containing the sheet names.
➤ Press OK.
So, our required drop-down list in Cell C4 is now ready to show the assigned values. Assuming that we have selected the option Sheet1 from the drop-down.
📌 Step 3:
➤ Now, in the output Cell C5, type the following formula:
=INDIRECT(C4&"!C5:C9")➤ Press Enter and you’ll see all donation amounts from the selected sheet in a vertical array at once.
📌 Step 4:
➤ Select another option (Sheet2) from the drop-down list now.
And the resultant output in the range of cells (C5:C9) will be updated immediately as shown in the following screenshot.
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
Concluding Words
I hope all of the methods mentioned above to use the INDIRECT function with a sheet name will now help you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.
