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

Sheet2 represents another chart for Day 2.

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

InÂ Cell C6, insert the formula below:

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

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

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

Read More: How to Use Excel INDIRECT Range

### Method 2 – 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.

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.

Step 3:

âž¤ In Cell C9, enter:

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

âž¤ Press Enter.

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

PressÂ Enter, the formula will return the expected total.

### Method 4 – 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.

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.

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:

âž¤ In the outputÂ Cell C5, insert the following formula:

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

âž¤ Press Enter.

Step 4:

âž¤ Select another option (Sheet2) from the drop-down list now.

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

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!