How to Pull Data from the Only Unhidden Excel Worksheet
Question details
The user needs a method to dynamically copy date values from the currently unhidden quote or invoice worksheet into a main billing worksheet.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Managing a billing workbook containing multiple quote and invoice worksheets where only one sheet is visible at a time, requiring data from the visible sheet to automatically populate a master summary sheet.
- Observed behavior
- Native spreadsheet formulas cannot directly detect worksheet visibility. Users struggle to automatically reference data based purely on a sheet's hidden or unhidden status.
Ensure that the data you want to extract (like start and end dates) is located in the exact same cell references across all your quote and invoice worksheets, and avoid using merged cells to prevent formula errors.
Use the INDIRECT Function with a Control Cell
Since formulas cannot directly read sheet visibility, manually typing the unhidden sheet's name into a designated control cell allows the INDIRECT function to dynamically pull the required data.
The INDIRECT function translates a text string into a valid cell reference. By referencing a cell that holds your active sheet's name, you can switch which worksheet your billing page pulls data from simply by updating that one text field.
On your billing worksheet, select a blank cell (for example, A1) and type the exact name of the currently unhidden quote or invoice worksheet.
Select the cell where you want the start date to appear. Enter the formula =INDIRECT("'"&A1&"'!C3") assuming C3 is the cell containing the start date on the quote sheets.
In the adjacent cell for the end date, enter =INDIRECT("'"&A1&"'!D3") replacing D3 with the correct reference for the end date.
Whenever you unhide a different sheet and hide the previous one, simply type the newly unhidden sheet's name into cell A1 to update your billing data instantly.
Use a VBA Macro to Auto-Detect the Unhidden Sheet
If you need the system to automatically detect which sheet is visible without manually typing its name, a VBA macro is the most robust solution.
Easily Pull and Manage Worksheet Data in WPS Office
WPS Spreadsheet fully supports advanced data referencing functions like INDIRECT and offers a highly compatible environment for handling dynamic data extraction from multiple quote and invoice sheets.
- 1. Open your workbook: Launch WPS Spreadsheet and open the billing workbook containing your hidden and unhidden sheets.
- 2. Set up a control cell: Choose a cell on your main summary page to type in the name of the currently visible quote worksheet.
- 3. Use the INDIRECT function: Input the INDIRECT formula referencing your control cell to seamlessly pull the dates into your master table.
- 4. Save and calculate: Press Enter to execute the formula. WPS Spreadsheet will instantly calculate and display the data from the referenced sheet.
Frequently Asked Questions
Can I write a formula that checks if an Excel sheet is hidden without using VBA?
No, standard spreadsheet formulas do not have built-in functions to evaluate a worksheet's visibility status. You must use a workaround like a control cell with the INDIRECT function or rely on a VBA macro to perform visibility checks.
Why does my INDIRECT formula return a #REF! error when referencing my quote sheet?
The #REF! error usually occurs if the sheet name in your control cell is misspelled, contains leading or trailing spaces, or if the referenced sheet does not exist. It can also happen if your formula is missing single quotes around a sheet name that contains spaces.
What happens to the INDIRECT formula if multiple quote sheets are unhidden at the same time?
The INDIRECT method targets the specific sheet name typed into your control cell, entirely ignoring visibility. Even if multiple sheets are unhidden, the formula will only pull data from the one explicitly named in that cell.
