INDIRECT Function with Sheet Name in Excel (4 Practical Uses)

Method 1 – INDIRECT Function with Sheet Name to Refer Another Worksheet

In the example image below, Sheet1 contains the names of donors and the donation.

INDIRECT Function with Sheet Name to Refer Another Worksheet

Sheet2 represents another chart for Day 2.

INDIRECT Function with Sheet Name to Refer Another Worksheet

In Sheet3, the data from the previous two sheets will be extracted.

INDIRECT Function with Sheet Name to Refer Another Worksheet

In Cell C6, insert the formula below:

=INDIRECT(C4&"!C5:C9")

Press Enter to get the donation amounts in an array from Sheet1.

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.

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

Read More: How to Use Excel INDIRECT Range


Method 2 – INDIRECT Function to Refer Sheet Name from Another Workbook in Excel

INDIRECT Function to Refer Sheet Name from Another Workbook in Excel

Step 1:

➤ Open a new workbook.

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

INDIRECT Function to Refer Sheet Name from Another Workbook in Excel

Step 2:

➤ Press Enter and the value of $1200 will be shown.

➤ Keep this Cell B4 in text format. Remove the Equal(=) symbol from the cell.

.➤ In Cells C6 and C7, type the names of the Excel workbook and the spreadsheet respectively from where the data will be extracted.

INDIRECT Function to Refer Sheet Name from Another Workbook in Excel

Step 3:

➤ In Cell C9, enter:

=INDIRECT("'["&C6&".xlsx]"&C7&"'!$C$7")

➤ Press Enter.

INDIRECT Function to Refer Sheet Name from Another Workbook in Excel


Method 3 – Numeric Calculation with INDIRECT Function While Referring to a Sheet Name

To calculate the total donation amount for Day 1 by using  the SUM function before the INDIRECT function, insert the formula below in

Cell C6:

=SUM(INDIRECT(C4&”!C5:C9″))

Numeric Calculation with INDIRECT Function While Referring to a Sheet Name

Press Enter, the formula will return the expected total.

Numeric Calculation with INDIRECT Function While Referring to a Sheet Name


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

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

Step 1:

➤Select Cell C4.

➤ Under Data, choose the Data Validation command from the 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

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:

➤ In the output Cell C5, insert the following formula:

=INDIRECT(C4&"!C5:C9")

➤ Press Enter.

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

The output in the range of cells (C5:C9) will be updated.

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


Download Practice Workbook


Related Articles


<< Go Back to Excel INDIRECT Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo