Retrieve Daily Data and Preserve Historical Values in WPS Spreadsheets
Question details
Retrieve data that matches each worksheet date and preserve the retrieved results as fixed historical values.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet uses date-based lookups to pull data from a source sheet for daily records.
- Observed behavior
- Lookup formulas update whenever the linked source data changes, so previously recorded daily values do not remain fixed.
Make sure the date values in both the record sheet and source sheet are real spreadsheet dates, not text. Confirm which source column contains the date and which column contains the value to return before entering the lookup formula.
Use XLOOKUP, then paste the daily results as values
Use this method when you want to retrieve the correct data for a date and lock that day's result after it has been reviewed.
A lookup formula remains linked to its source data and cannot permanently retain its current result by itself. After the daily values are correct, replace the formulas with their displayed values to preserve the historical record.
In the first result cell, such as B2, enter =XLOOKUP($A2,DataSheet!$A:$A,DataSheet!$B:$B,""). Replace DataSheet columns A and B with the source date column and source value column as needed.
Select the formula cell and drag the fill handle down, or use Home > Fill > Down, to retrieve values for the remaining dated rows.
Check that each date in column A returns the intended value from DataSheet before converting formulas to fixed values.
Select the completed formula cells, press Ctrl+C, then use Home > Paste > Paste Special > Values. The cells keep their displayed results and no longer change when DataSheet is updated.
Use INDEX and MATCH when XLOOKUP is unavailable
Use this alternative if your spreadsheet version does not support XLOOKUP or if you prefer a traditional lookup formula.
Use a daily closing process for new records
Use this process when the worksheet receives new source data every day and you need a consistent way to prevent old records from changing.
Create Daily Historical Records with WPS Spreadsheets
WPS Spreadsheets lets you retrieve values by date with XLOOKUP or INDEX/MATCH, then use Paste Special > Values to turn completed daily results into permanent historical records.
- 1. Set up date and result columns: Enter each reporting date in a worksheet column, such as column A, and reserve an adjacent column for the retrieved value.
- 2. Add a date-based lookup: Enter an XLOOKUP formula such as =XLOOKUP($A2,DataSheet!$A:$A,DataSheet!$B:$B,"") in the result column.
- 3. Fill the formula for active records: Drag the fill handle down to apply the formula to the rows that need current source data.
- 4. Lock completed daily results: Select the completed results, then choose Home > Paste > Paste Special > Values to replace the formulas with fixed values.
Frequently Asked Questions
Why do my historical lookup results change when the source sheet changes?
XLOOKUP, INDEX/MATCH, and other formulas recalculate from their referenced source cells. To keep a historical result, select the completed formula cells and use Home > Paste > Paste Special > Values.
Can a TODAY formula automatically retain yesterday's value?
No. TODAY() recalculates whenever the workbook recalculates, so it does not create a permanent daily snapshot. Use the formula to retrieve current data, then paste the completed result as a value.
How do I return a blank cell when the date is not found?
With XLOOKUP, use the fourth argument for a blank result: =XLOOKUP($A2,DataSheet!$A:$A,DataSheet!$B:$B,""). With INDEX/MATCH, wrap the formula in IFERROR(...,"").
Should I paste values over the whole worksheet?
Paste values only over the completed historical result range. Keep formulas in rows that still need to update from the source sheet.
Will value-only pasting work when I open the workbook in Microsoft Excel?
Yes. Fixed values are standard worksheet values and remain unchanged in compatible .xlsx and .xls files opened in Microsoft Excel or WPS Spreadsheets.
