Excel: Use INDIRECT to Reference Worksheets by Name
Question details
The user needs to retrieve values from worksheets whose names are listed in a summary column.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Multiple worksheets share the same layout, and a summary sheet needs to retrieve cell E4 from each worksheet named in column A.
- Observed behavior
- The desired result is a summary formula that uses each listed worksheet name to return the corresponding worksheet's E4 value.
Check that each name in the summary column exactly matches an existing worksheet tab, including spaces. Confirm that E4 contains the value you want to retrieve on each worksheet.
Use INDIRECT to retrieve E4 from the worksheet named in A2
Build a worksheet reference from a cell containing the sheet name, then fill the formula down to summarize multiple worksheets.
INDIRECT converts a text reference into an actual cell reference. Enclosing the worksheet name in single quotation marks supports names containing spaces and also works for simple worksheet names.
On your summary worksheet, enter the first worksheet name in A2 and additional worksheet names in A3, A4, and subsequent rows.
Select B2, enter =INDIRECT("'"&A2&"'!E4"), and press Enter. The formula returns the value from E4 on the worksheet named in A2.
Select B2 and drag the fill handle down alongside your worksheet-name list. A2 changes to A3, A4, and so on, while the target address E4 remains unchanged because it is inside quotation marks.
Click a referenced worksheet tab and compare its E4 value with the result on the summary sheet. To retrieve a different cell, replace E4 inside the formula with the required address.

Reference worksheets by name in WPS Spreadsheet
Use INDIRECT in WPS Spreadsheet to pull values from consistently structured worksheets into a summary sheet. Enter the worksheet names in one column and fill the formula down beside them.
- 1. Open the workbook: Launch WPS Spreadsheet and choose Open to load the workbook containing your source worksheets.
- 2. List the worksheet names: Select your summary worksheet and enter the source worksheet names in column A, starting at A2.
- 3. Create the reference: Click B2, type =INDIRECT("'"&A2&"'!E4"), and press Enter to retrieve E4 from the named worksheet.
- 4. Complete the summary: Select B2 and drag its fill handle down to the last worksheet name. Compare a returned value with E4 on the corresponding source worksheet.

Frequently Asked Questions
Why does my INDIRECT formula return #REF!?
Check the worksheet name in A2 against the actual sheet tab and confirm that the referenced cell address is valid. Missing sheets, extra spaces in the name, or malformed references can cause #REF!. Use =INDIRECT("'"&A2&"'!E4") when the sheet name contains spaces.
How do I reference a worksheet name that contains an apostrophe?
Escape each apostrophe by doubling it inside the reference text. With the worksheet name in A2, enter =INDIRECT("'"&SUBSTITUTE(A2,"'","''")&"'!E4") in the result cell.
Will E4 change to E5 when I fill the formula down?
No. In =INDIRECT("'"&A2&"'!E4"), E4 is a fixed text address, so it stays E4 when you fill down. The relative reference A2 changes to A3, A4, and subsequent rows to select different worksheet names.
Can INDIRECT retrieve a value from a closed Excel workbook?
Excel INDIRECT requires the referenced external workbook to be open. Open both workbooks before using an external INDIRECT reference; otherwise, the formula returns #REF!. References to worksheets within the current workbook do not have this restriction.




