WPS Spreadsheet: Use Today’s Month to Pull Data from Monthly Sheets
Question details
The user needs a formula that detects the current month and retrieves a value from the worksheet named after that month.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A workbook contains separate worksheets for each month, and the user wants one formula to pull data from the current month’s worksheet automatically.
- Observed behavior
- The formula must create a dynamic worksheet reference and may return an error when the expected monthly tab is missing or named differently.
Check that the monthly worksheet tabs use the full month names exactly, such as January, February, and March. Confirm which cell should be retrieved from each tab, such as B2.
Use TODAY, TEXT, and INDIRECT to reference the current month sheet
Use this formula when every worksheet is named with the full English month and the target cell has the same address on each sheet.
TEXT(TODAY(),"mmmm") converts the current date into the full month name. INDIRECT then combines that name with the target cell reference to create a worksheet reference dynamically.
Open the summary or dashboard worksheet in WPS Spreadsheet and click the cell where you want the current month’s value to appear.
Type =INDIRECT("'"&TEXT(TODAY(),"mmmm")&"'!B2") in the formula bar, then press Enter. Replace B2 with the cell address you need to retrieve.
Look at the worksheet tabs and confirm that the current month’s tab uses the exact full name returned by TEXT, including spelling and spaces.
Build dynamic monthly worksheet lookups in WPS Spreadsheet
WPS Spreadsheet can automatically identify the current month and retrieve a matching value from a monthly worksheet using TODAY, TEXT, and INDIRECT. Its familiar formula tools make it easy to maintain monthly reports and dashboards.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the monthly worksheets.
- 2. Create or select the output cell: Go to the summary worksheet and click the cell that should display the current month’s result.
- 3. Enter the formula: Use =INDIRECT("'"&TEXT(TODAY(),"mmmm")&"'!B2") in the formula bar, changing B2 to the required source cell.
- 4. Check the worksheet naming: Compare the current month shown by the system date with the corresponding worksheet tab and correct any spelling differences.
Frequently Asked Questions
What formula pulls data from the worksheet for the current month?
In WPS Spreadsheet, enter =INDIRECT("'"&TEXT(TODAY(),"mmmm")&"'!B2"). The formula converts today’s date into a full month name and retrieves cell B2 from the matching worksheet.
Why does the INDIRECT formula return an error?
The formula returns an error if the worksheet for the current month does not exist or its tab name differs from the result of TEXT(TODAY(),"mmmm"). Check spelling, spaces, and whether the tab uses the full month name.
Can I retrieve a different cell instead of B2?
Yes. Replace B2 at the end of the formula with another cell reference, such as D5: =INDIRECT("'"&TEXT(TODAY(),"mmmm")&"'!D5"). Use the same source address on each monthly worksheet.
How does the formula change when worksheet names use month abbreviations?
If tabs are named Jan, Feb, and Mar, change the TEXT format to "mmm": =INDIRECT("'"&TEXT(TODAY(),"mmm")&"'!B2"). The tab names must match the abbreviation format exactly.
