INDIRECT Function with Sheet Name in Excel (4 Criteria)

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.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


4 Uses of INDIRECT Function with Sheet Name in Excel

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.

INDIRECT Function with Sheet Name to Refer Another Worksheet

Similarly, Sheet2 represents another chart for Day 2.

INDIRECT Function with Sheet Name to Refer Another Worksheet

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.

INDIRECT Function with Sheet Name to Refer Another Worksheet

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.

INDIRECT Function with Sheet Name to Refer Another Worksheet

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

INDIRECT Function with Sheet Name to Refer Another Worksheet


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.

INDIRECT Function to Refer Sheet Name from Another Workbook in Excel

📌 Step 1:

➤ Let’s open another new workbook first.

➤ In Cell B4, type ‘=’ and click on the Cell C7 of the Donation Data workbook.

INDIRECT Function to Refer Sheet Name from Another Workbook in Excel

📌 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.

INDIRECT Function to Refer Sheet Name from Another Workbook in Excel

📌 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.

INDIRECT Function to Refer Sheet Name from Another Workbook in Excel


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.

Numeric Calculation with INDIRECT Function While Referring to a Sheet Name

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.

Numeric Calculation with INDIRECT Function While Referring to a Sheet Name


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.

Use of Drop Down to Input Sheet Name in the INDIRECT Function in Excel

📌 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.

Use of Drop Down to Input Sheet Name in the INDIRECT Function in Excel

📌 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.

Use of Drop Down to Input Sheet Name in the INDIRECT Function in Excel

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.

Use of Drop Down to Input Sheet Name in the INDIRECT Function in Excel

📌 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.

Use of Drop Down to Input Sheet Name in the INDIRECT Function in Excel

📌 Step 4:

➤ Select another option (Sheet2) from the drop-down list now.

Use of Drop Down to Input Sheet Name in the INDIRECT Function in Excel

And the resultant output in the range of cells (C5:C9) will be updated immediately as shown in the following screenshot.

Use of Drop Down to Input Sheet Name in the INDIRECT Function in Excel


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.


You May Also Like to Explore

INDIRECT VLOOKUP in Excel

Formula Using INDIRECT INDEX MATCH Functions in Excel

How to Use INDIRECT Function in Excel (12 Suitable Instances)

INDIRECT Function Excel: Get values from different sheet

Practice Excel Indirect Function with 3 Case Studies

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo