Display a Selected Excel Worksheet on a Summary Sheet
Question details
Create a summary sheet that displays data from a user-selected monthly worksheet while the source worksheets remain hidden.

- 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.
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.
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.
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.
Select B1, then choose Data > Data Validation. Under Allow, select List, enter =$H$2:$H$13 as the source, and click OK.
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.
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.
Right-click each monthly worksheet tab and select Hide. Change the selection in B1 to check that the summary updates.

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. Open the workbook: Open your Excel workbook in WPS Spreadsheets and select or add a summary worksheet.
- 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. 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. 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.

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.




