How to Pull Dates from Multiple Excel Worksheets Using Sheet Names
Question details
The user wants to dynamically retrieve data, specifically a Start Date in cell B4, from multiple worksheets by referencing a list of worksheet names.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a summary sheet that aggregates dates from various meeting sheets as new ones are added to the workbook.
- Observed behavior
- Needs a scalable formula to pull the value from cell B4 of each corresponding meeting sheet based strictly on its name in a lookup list.
Ensure all your meeting worksheets are already created and that their tab names exactly match the list you plan to use in your summary sheet, as mismatched names will result in formula errors.
Use the INDIRECT Function to Pull Data Dynamically
The INDIRECT function converts a text string into a valid cell reference, allowing you to pull data from sheets based on names listed in a column.
The INDIRECT function is perfect for building dynamic formulas across large workbooks. By referencing a cell that contains a sheet name, the software can locate that specific sheet and retrieve the target cell's value without you having to manually click and link each sheet.
Enter the exact names of your meeting worksheets in a designated column (for example, cells A1 to A3 on a sheet named 'Lookup_sheets').
On your Summary sheet, click the cell where you want the date to appear. Type the formula: =INDIRECT("'"&Lookup_sheets!A1&"'!B4")
Press Enter to execute the formula. Click the cell again, grab the fill handle (the small square at the bottom-right corner), and drag it down beside your list of sheet names.
Whenever a new meeting sheet is created, simply add its exact name to the bottom of your lookup list and drag the formula down one more row to retrieve its date.
Effortlessly Manage Multiple Worksheets with WPS Spreadsheet
WPS Spreadsheet fully supports the INDIRECT function and advanced dynamic arrays, making it easy to consolidate data from dozens of sheets. It is a powerful, free alternative designed to streamline your daily data analysis.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your multiple meeting sheets.
- 2. Set up your list: Create a summary sheet and type the names of the existing meeting tabs in a single column.
- 3. Insert the formula: Type =INDIRECT("'"&A1&"'!B4") into the adjacent cell, replacing A1 with the cell containing your first sheet name, and press Enter.
- 4. Fill down: Drag the fill handle downward to instantly pull dates from all the sheets listed.
Frequently Asked Questions
Why is my INDIRECT formula returning a #REF! error?
A #REF! error typically occurs if the worksheet name in your lookup list doesn't match the actual sheet tab name exactly, or if the referenced worksheet hasn't been created yet. Double-check for trailing spaces in both the cell and the tab name.
Does the INDIRECT function work if referencing a closed external workbook?
No, the INDIRECT function requires the referenced workbook to be open. If you are linking to an external workbook and it is closed, the formula will return a #REF! error. However, it works perfectly across multiple sheets within the same open file.
How can I hide the error if the meeting sheet isn't created yet?
You can wrap your formula in the IFERROR function to handle missing sheets gracefully. For example, using =IFERROR(INDIRECT("'"&Lookup_sheets!A1&"'!B4"), "") will leave the cell blank instead of displaying an error until the sheet is eventually created.
Why are there single quotes around the sheet name in the formula structure?
The single quotes (') are necessary when worksheet names contain spaces or special characters. Including them in your INDIRECT formula structure ensures the reference doesn't break, regardless of how you name your sheet tabs.
