Function Problems

Use a Date to Reference a Numbered Worksheet in Excel

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 868 views

Question details

Create a dynamic reference to a numbered worksheet using the day of a date.

Use a Date to Reference a Numbered Worksheet in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A workbook has daily worksheets named 1 through 31, and a date determines which sheet to reference.
Observed behavior
The formula should return the value from the same cell on the worksheet whose number matches the day of the date.
Before you start

Check that the date cell contains a valid Excel date and that the daily worksheets are named with numbers such as 1, 2, and 31. Identify the cell to retrieve from each sheet, such as H8.

Solution 1Recommended

Use INDIRECT and DAY to Reference the Daily Worksheet

Build a worksheet reference from the day of the date and return a blank if the reference produces an error.

DAY extracts the day number, and INDIRECT converts the assembled text into a worksheet reference. The single quotation marks enclose the worksheet name.

The formula uses only the day of the month, not the month or year. IFERROR hides all errors from the reference, including errors in the source cell.

1
Select the result cell

Click the cell where you want to display the value from the daily worksheet.

2
Enter the formula

If the date is in A2 and the source value is in H8, enter =IFERROR(INDIRECT("'"&DAY($A2)&"'!$H$8"),"") and press Enter.

3
Adjust the references

Replace $A2 with your date cell and $H$8 with the cell to retrieve. For a date in B12, use =IFERROR(INDIRECT("'"&DAY(B12)&"'!$H$8"),"").

4
Check the result

Change the date or inspect the matching numbered sheet. For example, a date on the 15th should return the value from cell H8 on sheet 15.

Use INDIRECT and DAY to Reference the Daily Worksheet
Troubleshoot a blank result: Check the date, sheet name, and source cell. To expose an underlying error temporarily, remove the IFERROR wrapper and enter =INDIRECT("'"&DAY($A2)&"'!$H$8").
WPS Spreadsheets

Create Date-Based Worksheet References in WPS Spreadsheets

Use DAY, INDIRECT, and IFERROR in WPS Spreadsheets to retrieve a value from a numbered daily worksheet based on a date.

  1. 1. Open the workbook: Open WPS Spreadsheets and select Open to load the workbook containing the numbered daily sheets.
  2. 2. Select a result cell: Click the cell where you want the value from the daily sheet to appear.
  3. 3. Enter the formula: Enter =IFERROR(INDIRECT("'"&DAY($A2)&"'!$H$8"),"") and press Enter. Replace $A2 and $H$8 with your date and source cell references if needed.
  4. 4. Verify the reference: Check the worksheet tab matching the day of the date and confirm that its H8 value appears in the result cell.
Reference daily sheets dynamically using the day of a date.Open and edit Microsoft Excel XLSX workbooks.Use familiar spreadsheet formulas and worksheet tabs.Work with a free, lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

How do I use a date in B12 instead of A2?

Enter =IFERROR(INDIRECT("'"&DAY(B12)&"'!$H$8"),"") in the result cell. Change $H$8 if the value is in a different cell on each daily sheet.

Why does the formula return a blank?

IFERROR returns a blank when the formula encounters an error. Check that the date is valid, the matching numbered sheet exists, and the source cell does not contain an error.

Does the formula account for the month and year?

No. DAY extracts only the day of the month, so dates with the same day number reference the same numbered worksheet.

Can I copy the formula down for several dates?

Yes. With $A2 as the date reference, drag the fill handle down to use A3, A4, and subsequent rows. The $H$8 reference remains fixed on each selected sheet.