Copy Data Between Workbooks Based on Date Sheet Names in WPS Spreadsheets
Question details
User needs to retrieve values from a source workbook when the source worksheet name matches a date listed in a destination workbook, using WPS Spreadsheets.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has two Excel workbooks: a destination workbook with date values and a source workbook with worksheets named by those dates. They want cell values to be pulled automatically based on the matching sheet name.
- Observed behavior
- The user needs a dynamic formula rather than manually changing the worksheet reference for each date.
Open both workbooks in WPS Spreadsheets and verify that the date sheet names match the date values in the destination workbook exactly, including leading zeros, spaces, or text formatting.
Use an INDIRECT Formula to Reference Date-Named Worksheets
Use this when the source workbook layout is consistent and the destination cell contains a date value that matches a worksheet tab name.
INDIRECT converts a text string into a real reference, so you can combine the source workbook name, the date-based sheet name, and the target cell in one formula.
Because INDIRECT is volatile, it recalculates whenever any workbook change occurs. It is best for moderate-sized workbooks or temporary linking.
Open the source workbook and the destination workbook. Note the exact source file name, including the .xlsx or .xls extension.
Click the cell where the result should appear. Confirm which cell holds the date value, for example A2.
Type =INDIRECT("'[SourceFileName.xlsx]"&A2&"'!B2") in the formula bar, replacing SourceFileName.xlsx with the actual source workbook name and B2 with the source sheet cell.
Press Enter. WPS Spreadsheets returns the matching value when the source workbook is open and the text in A2 exactly matches the sheet name.
Use Helper Cells to Build the Source Reference for Easier Auditing
Use this when several date rows are involved and you want to edit the workbook name or sheet-name format in one place instead of rewriting each formula.
Link workbooks by date-named sheets with WPS Spreadsheets
WPS Spreadsheets can directly complete this task with INDIRECT formulas. Open your source and destination workbooks, build a date-driven worksheet reference, and retrieve the matching values without manually switching between files.
- 1. Download and open WPS Office: Install WPS Office, then open your destination workbook and source workbook in WPS Spreadsheets.
- 2. Confirm the date and sheet names: Check that each date value in the destination matches the corresponding worksheet tab name exactly.
- 3. Enter the INDIRECT reference: Click the destination cell and type =INDIRECT("'[Source.xlsx]"&A2&"'!B2"), replacing Source.xlsx and B2 with your file and cell.
- 4. Save the workbook: Press Ctrl+S to save the destination workbook with the new dynamic formula.
Frequently Asked Questions
Why does INDIRECT return #REF! when the source workbook is open?
#REF! usually means the text string does not match a valid reference. Check that the source workbook name, sheet name, and cell address are spelled exactly, and include single quotes around the workbook and sheet portion when the name contains spaces.
Can INDIRECT pull data from a closed workbook in WPS Spreadsheets?
No. In WPS Spreadsheets, INDIRECT generally requires the source workbook to be open. If the source workbook is closed, use direct workbook links, import the data, or use a supported data connection instead.
How do date-formatted cells work as worksheet names with INDIRECT?
A date cell may be stored as a number even if it appears as a date. Use TEXT(A2,"yyyy-mm-dd") or match the exact text format used on the worksheet tab so the date converts to the same text string.
Does INDIRECT slow down a large WPS workbook?
Yes. INDIRECT is volatile and recalculates whenever any change is made. For large or frequently edited workbooks, limit the number of INDIRECT formulas or replace them with direct references after setup.
