Function Problems

Display a Selected Excel Worksheet on a Summary Sheet

John WilsonJohn Wilson Nov 18, 2026 868 views

Question details

Create a summary sheet that displays data from a user-selected monthly worksheet while the source worksheets remain hidden.

Display a Selected Excel Worksheet on a Summary Sheet
Product
Microsoft Excel
Device & OS
not provided
Scenario
A workbook has many similarly named monthly worksheets, and the user wants to choose which one appears on a single display sheet.
Observed behavior
The desired summary sheet should update its displayed data when the user selects a different monthly worksheet.
Before you start

Check that the monthly worksheets use the same cell layout and note their exact tab names. Keep the summary sheet visible so users can change the selection.

Solution 1Recommended

Use a worksheet selector and INDIRECT formulas

Use this method to display the same cell range from whichever monthly worksheet the user selects.

INDIRECT builds a reference from the selected sheet name, including when the source sheet is hidden. The formula below also handles sheet names containing apostrophes.

1
List the worksheet names

On the summary sheet, enter the exact monthly worksheet names in cells H2:H13, or use another available column and adjust the range to fit your list.

2
Create a selector

Select B1, then choose Data > Data Validation. Under Allow, select List, enter =$H$2:$H$13 as the source, and click OK.

3
Display the first source cell

Select a worksheet name in B1. In A3, enter =INDIRECT("'"&SUBSTITUTE($B$1,"'","''")&"'!"&ADDRESS(ROW(A1),COLUMN(A1),4)) to display cell A1 from the selected sheet.

4
Fill the display range

Drag the fill handle from A3 across and down to cover the source range you need. The formulas will reference corresponding cells on the selected sheet.

5
Hide the monthly sheets

Right-click each monthly worksheet tab and select Hide. Change the selection in B1 to check that the summary updates.

Use a worksheet selector and INDIRECT formulas
Keep sheet names in sync: If you rename a monthly tab, update its name in the selector list. INDIRECT is volatile, so many formulas may slow down a large workbook.
WPS Spreadsheets

Build a Monthly Worksheet Selector in WPS Spreadsheets

Use a dropdown and INDIRECT formulas in WPS Spreadsheets to show data from a selected monthly worksheet on one summary sheet.

  1. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and select or add a summary worksheet.
  2. 2. Add the selector list: Enter the monthly tab names in H2:H13. Select B1, choose Data > Data Validation, select List, and set the source to =$H$2:$H$13.
  3. 3. Enter the display formula: Choose a sheet name in B1. In A3, enter =INDIRECT("'"&SUBSTITUTE($B$1,"'","''")&"'!"&ADDRESS(ROW(A1),COLUMN(A1),4)), then drag the fill handle across and down.
  4. 4. Hide source tabs and test: Right-click each monthly sheet tab and choose Hide. Select another name in B1 to confirm the summary changes, then save the workbook.
Switch the summary display between monthly sheets with a dropdown.Open and edit Microsoft Excel XLSX workbooks.Use familiar spreadsheet formulas and worksheet tabs.Work with a free, easy-to-use office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Can INDIRECT reference a hidden worksheet?

Yes. A formula can reference a hidden worksheet as long as the sheet name in B1 matches an existing tab and the referenced cells are valid.

Why does the summary show a #REF! error?

Check that the name selected in B1 exactly matches a worksheet tab and that the referenced cell exists. If you renamed a tab, update its entry in the selector list.

Does hiding a worksheet protect its data?

No. Hiding a tab keeps it out of view, but users can normally right-click a visible sheet tab and choose Unhide. Use appropriate protection and access controls for sensitive data.

Is there a better layout for comparing many months?

For frequent reporting and comparisons, consider combining the monthly records into one structured table with a month column. You can then use filters or a PivotTable to summarize the data.