Function Problems

Use Excel INDIRECT to Reference a Cell on a Selected Worksheet

John WilsonJohn Wilson Nov 14, 2026 869 views

Question details

Display the tax calculated on the worksheet selected by a drop-down list.

Product
Microsoft Excel
Device & OS
not provided
Scenario
A filing-status drop-down in B2 selects a worksheet, and B7 on the first worksheet should display that worksheet's tax result from A15.
Observed behavior
The desired result is for B7 to update when the selected worksheet name changes.
Before you start

Check that the drop-down in B2 contains the worksheet names and that each filing-status worksheet calculates its result in A15. The selected text must match the worksheet name exactly.

Solution 1Recommended

Use INDIRECT to reference the selected worksheet

Use this formula to display A15 from whichever worksheet is named in B2, without nested IFS statements.

INDIRECT converts the text assembled from B2 into a worksheet reference. The single quotes around the worksheet name allow names containing spaces.

1
Select the result cell

On the first worksheet, click cell B7.

2
Enter the formula

Type =INDIRECT("'"&B2&"'!A15") in B7 and press Enter.

3
Test the drop-down

Choose a worksheet name from the drop-down in B2 and confirm that B7 displays the value from that worksheet's A15.

Worksheet names with apostrophes: If a worksheet name contains an apostrophe, use =INDIRECT("'"&SUBSTITUTE(B2,"'","''")&"'!A15") to escape it.
Work with worksheets in WPS

Reference a Selected Worksheet in WPS Spreadsheet

Open your workbook in WPS Spreadsheet and use INDIRECT to show the value from A15 on the worksheet selected in B2.

  1. 1. Open the workbook: Open WPS Spreadsheet and select Open to load your workbook.
  2. 2. Check the selection: On the first worksheet, use the drop-down in B2 to select a name that matches a worksheet tab.
  3. 3. Enter the reference: Click B7, enter =INDIRECT("'"&B2&"'!A15"), and press Enter.
  4. 4. Verify the result: Change the selection in B2 and check that B7 displays A15 from the newly selected worksheet.
Update the displayed tax result when the worksheet selection changesUse an INDIRECT formula instead of nested IFS statementsOpen and save Microsoft Excel XLSX filesWork in a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does the INDIRECT formula return #REF!?

Check the value selected in B2 against the worksheet tab name. An invalid or misspelled worksheet reference causes #REF!. If the name contains an apostrophe, use SUBSTITUTE to double it in the formula.

Can the worksheet name contain spaces?

Yes. The formula encloses the name from B2 in single quotes, so a worksheet name such as Married Filing Jointly can be referenced.

How do I return a different cell from the selected worksheet?

Edit the formula in B7 and replace A15 with the desired cell address. For example, use =INDIRECT("'"&B2&"'!C10") to return C10.

Do I need an IFS formula for each filing status?

No. If each drop-down value matches a worksheet name and every result is in A15, one INDIRECT formula can reference the selected worksheet.