Function Problems

Retrieve Data for a Selected Month from Another Excel Sheet

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 869 views

Question details

Automatically retrieve rows from another worksheet for a selected date or month.

Retrieve Data for a Selected Month from Another Excel Sheet
Product
Microsoft Excel
Device & OS
not provided
Scenario
A report should update when its selected date changes each month.
Observed behavior
The report should display the source rows matching the selected date or the month containing it.
Before you start

Ensure the source date column contains real Excel dates rather than text. The examples use a Source sheet with dates in A2:A1000 and data in A2:D1000, plus a Report sheet with the selected date in B1.

Solution 1Recommended

Use FILTER to return rows for the selected month

Use this approach in a version of Excel that supports dynamic-array FILTER formulas.

1
Choose a date

On the Report sheet, enter a date such as July 31, 2021 in cell B1. Any date in the desired month will work.

2
Add an optional drop-down

To select from a list, enter valid dates in an unused range on the Report sheet. Select B1, choose Data > Data Validation, set Allow to List, and select that range as the Source.

3
Enter the month formula

In an empty cell on Report, enter =FILTER(Source!A2:D1000,(Source!A2:A1000>=DATE(YEAR(B1),MONTH(B1),1))*(Source!A2:A1000<EDATE(DATE(YEAR(B1),MONTH(B1),1),1)),"No matching rows").

4
Change the selected month

Change B1 or choose another date from its drop-down. The formula returns rows from the start of that month up to, but not including, the start of the next month.

Use FILTER to return rows for the selected month
Allow room for the results: FILTER spills matching rows into neighboring cells. Clear any cells blocking the output, and adjust the source ranges if your data extends beyond row 1000.
Work with monthly data in WPS

Retrieve monthly rows in WPS Spreadsheet

Use a helper column and lookup formulas in WPS Spreadsheet to update a report when you change its selected date.

  1. 1. Prepare the workbook: Open the workbook in WPS Spreadsheet. On a sheet named Source, place dates in A2:A1000 and the data to return in A2:D1000.
  2. 2. Set the report date: On a sheet named Report, enter a valid date in B1. Leave A3:D1001 available for the results.
  3. 3. Number matching rows: On Source, enter a header in E1. In E2, enter =IF(AND(ISNUMBER(A2),A2>=DATE(YEAR(Report!$B$1),MONTH(Report!$B$1),1),A2<EDATE(DATE(YEAR(Report!$B$1),MONTH(Report!$B$1),1),1)),COUNT($E$1:E1)+1,"") and fill it down through E1000.
  4. 4. Retrieve the first result: On Report, enter =IFERROR(INDEX(Source!$A$2:$D$1000,MATCH(ROWS($A$3:A3),Source!$E$2:$E$1000,0),COLUMNS($A$3:A3)),"") in A3.
  5. 5. Fill the report: Copy A3 across through D3, then fill those cells down through row 1001. Change B1 to update the matching rows; format the returned date column as a date if needed.
Automatically refresh monthly results when the selected date changesOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet formulas and worksheet navigation
microsoft office alternative - wps office

Frequently Asked Questions

How do I match one exact date instead of a whole month?

In Excel, use =FILTER(Source!A2:D1000,Source!A2:A1000=Report!B1,"No matching rows"). This exact comparison requires the source values and selected date to have matching time values as well.

Why does the FILTER formula show a #SPILL! error?

Clear any cells blocking the output area below or beside the formula. Enter the formula outside an Excel table, since spilled array formulas cannot spill inside a table.

Why are some dates missing from the results?

Check that the source dates and Report!B1 are real date values, not text. Also verify that the formula's source ranges include all relevant rows.

Does selecting July 31 return only the last day of July?

Not with the monthly formula: it returns all rows from July 1 up to August 1. Use the exact-date formula if you only want July 31.