Function Problems

Excel: Use INDIRECT to Reference Worksheets by Name

Phi Hung VoPhi Hung Vo Nov 13, 2026 870 views

Question details

The user needs to retrieve values from worksheets whose names are listed in a summary column.

Excel: Use INDIRECT to Reference Worksheets by Name
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the worksheet names

On your summary worksheet, enter the first worksheet name in A2 and additional worksheet names in A3, A4, and subsequent rows.

2
Enter the INDIRECT formula

Select B2, enter =INDIRECT("'"&A2&"'!E4"), and press Enter. The formula returns the value from E4 on the worksheet named in A2.

3
Fill the formula down

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.

4
Check the returned values

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.

Use INDIRECT to retrieve E4 from the worksheet named in A2
Short formula for simple worksheet names: For worksheet names that do not require quotation marks, =INDIRECT(A2&"!E4") also works. Use the quoted version for names with spaces; embedded apostrophes require additional escaping.
Build worksheet summaries in WPS

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. 1. Open the workbook: Launch WPS Spreadsheet and choose Open to load the workbook containing your source worksheets.
  2. 2. List the worksheet names: Select your summary worksheet and enter the source worksheet names in column A, starting at A2.
  3. 3. Create the reference: Click B2, type =INDIRECT("'"&A2&"'!E4"), and press Enter to retrieve E4 from the named worksheet.
  4. 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.
Retrieve values dynamically using worksheet names stored in cells.Supports Microsoft Excel .xlsx and .xls workbook formats.Use familiar formula entry and fill-handle controls.Handle worksheet names containing spaces with quoted references.
microsoft office alternative - wps office

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.