How to Use Date-Based Filenames in Excel VLOOKUP from SharePoint with WPS Office
Question details
The user needs to make an Excel VLOOKUP formula reference an external SharePoint workbook whose file name changes by date, and understand why a standard VLOOKUP cannot update the filename automatically.
- Product
- WPS Office Spreadsheets and SharePoint
- Device & OS
- not provided
- Scenario
- The user has daily or date-based workbooks in SharePoint and wants a single formula to pull data from the correct day's file without manually editing the reference each time.
- Observed behavior
- A standard VLOOKUP cannot automatically change the external SharePoint workbook filename based on a date cell; closed-workbook reference changes require INDIRECT with an open source file or another data-loading method.
Check that the SharePoint daily files follow a consistent naming pattern and note whether the source workbook will be open during calculation. If files stay closed, prepare to use Power Query/import or create a consolidated data file instead of relying on INDIRECT.
Build the Filename with INDIRECT for an Open Workbook
Use this when the date-based SharePoint workbook is already open in WPS Office Spreadsheets and you need a quick formula-based reference.
INDIRECT can convert a date cell into a filename string. However, in Excel-compatible spreadsheet applications, INDIRECT generally only calculates an external reference when the source workbook is open.
In WPS Office Spreadsheets, enter the date in cell A1. In another cell, enter =TEXT(A1,"yyyy-mm-dd")&"_Sales.xlsx" to create the daily file name.
Build a text string in cell C1 that contains the full SharePoint file URL and sheet range, for example ="'https://yourtenant.sharepoint.com/sites/YourSite/Shared%20Documents/"&A1&"_Sales.xlsx'!A:B". Then use =VLOOKUP(B2,INDIRECT(C1),2,FALSE).
Open the date-based workbook from SharePoint in WPS Office Spreadsheets, then recalculate the sheet with F9 so INDIRECT returns values from the open external file.
Combine Closed Date-Based Files with Power Query or Data Import
Use this for closed SharePoint daily files when you want a refreshable import without manually opening each workbook.
Update the VLOOKUP Reference with VBA or Office Scripts
Use this for automated daily or monthly reporting when you want the formula path to be rewritten programmatically.
Use WPS Office as a Free, Excel-Compatible Spreadsheet for Date-Based Files
WPS Office is a free, lightweight alternative that opens .xlsx and .xls workbooks from SharePoint, supports familiar formulas such as VLOOKUP and INDIRECT, and offers a familiar ribbon interface for easy migration. It is ideal for editing date-based Excel files even when the closed-workbook dynamic reference limitation must be handled with an import routine.
- 1. Download WPS Office: Click Download WPS Office Free and install WPS Office on your Windows or Mac computer.
- 2. Open the date-based workbook: Launch WPS Office Spreadsheets, click File > Open, and open the .xlsx file downloaded or synchronized from SharePoint.
- 3. Work with Excel-compatible formulas: Use the same VLOOKUP, INDIRECT, and date functions to review or edit the date-based data before saving it back in Excel format.
Frequently Asked Questions
Can VLOOKUP in WPS Office automatically change a SharePoint filename based on a date?
No. A standard VLOOKUP cannot change an external filename by itself. You can construct the reference with INDIRECT if the external workbook is open, or use Power Query/Data import to load closed daily files.
Does INDIRECT work with closed SharePoint workbooks?
Generally no. INDIRECT can build a filename from a date cell, but the external workbook must be open in the same spreadsheet application for the formula to calculate reliably.
What is the best way to combine daily date-based Excel files from SharePoint?
Use Power Query or the Data tab import tools to connect to the SharePoint folder, filter by file name or date, and load or combine the selected file into one table.
Can I automate the VLOOKUP filename update with a macro?
Yes. You can use VBA or Office Scripts to read a date cell, build the new path, and rewrite the VLOOKUP reference, but VBA support depends on your WPS Office version and environment.
Will WPS Office open date-based .xlsx files from SharePoint?
Yes. WPS Office Spreadsheets can open, edit, and save .xlsx files downloaded from SharePoint, and it supports Excel-compatible formulas such as VLOOKUP and INDIRECT.
