Writing a Formula in Excel to search data on one sheet to copy to another

Question

Writing a Formula in Excel to search data on one sheet to copy to another

Richard Keenan 20

Using the following information; how do I write a formula to search a column on one sheet, if the date entered into any of the cells in that column meet todays date or are three months from today, copy that rows information to a new sheet?

Sheet to search: TRW

Column to Search: E

Data Range on Sheet: A2:E22

Filter for: = to todays date or 3 months in the future

Sheet to transfer to is: Due

doni 0 Reputation points

2026-05-29T15:57:33.8+00:00

no problem

Answer accepted by question author

0 additional answers

Your answer

doni 0 Reputation points

2026-05-29T15:57:33.8+00:00

no problem

Answer 1

Hendrix-C 20,015 Microsoft External Staff Moderator

Hi @Richard Keenan,

Please notify me if I have misunderstood anything about your situation.

Based on your sharing, I created a sample data like this

User's image

Then I go to the Due sheet and input the formula into cell A2 =FILTER(TRW!A2:E22,(TRW!E2:E22>=TODAY())*(TRW!E2:E22<=EDATE(TODAY(),3)), "No matching dates"). It will return full matching rows where the date is equal today's date or within 3 months from today.

User's image

If your expected outcome is return rows where date is equal today and equal to the date 3 months from today (exact 3-month date, not within 3 months) only, use this formula instead =FILTER(TRW!A2:E22,(TRW!E2:E22=TODAY())+(TRW!E2:E22=EDATE(TODAY(),3)), "No matching dates")

User's image

Hope my sharing may be helpful for your concern. If you have any questions or need any further clarification, please leave a comment here and I will be happy to continue to help you.

Thank you for your patience and understanding. I'm looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Richard Keenan 20 Reputation points

2026-05-28T19:54:46.8233333+00:00

Thank you VERY MUCH!!!!!! It works great.

If you would indulge me to ask a follow-on question. If I have other sheets in the same book and I want to do the same to them and add the selected rows to the following blank rows is that possible?

TIA

[Moderator note: Personally Identifiable Information removed]
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-05-28T20:41:08.83+00:00
Hi @Richard Keenan,

Yes, it is possible. To apply the filter conditions to multiple sheets, you can use this formula:

=LET(allsheets, VSTACK(TRW!A2:E22,ABC!A2:E22), duedate, CHOOSECOLS(allsheets,5), FILTER(allsheets,(duedate>=TODAY())*(duedate<=EDATE(TODAY(),3)),"No matching rows"))

I only created a copy of TRW and rename as ABC sheet for demonstration. If you have more sheets in your workbook, you need to add the range inside the VSTACK() part. For example, if I have one more XYZ sheet with the same data range A2:E22, I will add it in the VSTACK part like this VSTACK(TRW!A2:E22, ABC!A2:E22, XYZ!A2:E22).

One important note is that the Date column must be the 5th column in all of the selected ranges (column E if starts from A) so the formula can work properly.

You can try the formula and let me know if it works for your situation. If there is any further question, feel free to reach out again and I'll be happy to help.

Thank you for your cooperation and understanding. Hope to hear from you soon.
Richard Keenan 20 Reputation points

2026-05-29T14:47:04.7533333+00:00

I put this formula in and have checked it three times to insure I typed it in correctly. It keeps coming up with a popup message box that says: "You can't include a parameter to a LET function after defining its calculation."
Richard Keenan 20 Reputation points

2026-05-29T15:01:19.78+00:00

Sorry, how rude of me, I really appreciate you and all the help you are providing me.
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-05-29T15:38:54.0466667+00:00
Hi @Richard Keenan,

Please don't worry too much since I'm here to help and ensure your issue is well-resolved. So, if any problems arise while trying the formula, I would be happy if you could update me promptly so I can assist you in a timely manner.

The error "You can't include a parameter to a LET function after defining its calculation." means that there is a variable is defined after calculation within the LET formula. The formula I provided

=LET(allsheets, VSTACK(TRW!A2:E22,ABC!A2:E22), duedate, CHOOSECOLS(allsheets,5), FILTER(allsheets,(duedate>=TODAY())*(duedate<=EDATE(TODAY(),3)),"No matching rows"))

has two defined variables: allsheets and duedate, and they must be defined before the calculation FILTER().

Therefore, I suspect that maybe you have missed a comma(,) somewhere in the formula so the LET formula cannot define the variable and return the error. May I ask whether you still receive the error message? If yes, please feel free to share me a screenshot of the formula you are using and I will be happy to help you double check and correct it if there is any syntax issue.

Thank you for your patience and cooperation. I'm looking forward to hearing from you.
Richard Keenan 20 Reputation points

2026-05-29T15:54:42.3666667+00:00

=LET(allsheets, VSTACK(TRW!A2:E22,TRF!A2:E22,TOS!A2:E22,TOE!A2:E22,TOJF!A2:E22,TOJE!A2:E22,TOJS!A2:E22),duedate, CHOOSECOLS(allsheets,5), FILTER(allsheets,(duedate>=TODAY())*(duedate<=EDATE(TODAY(),3)),"No matching rows"))
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-05-29T16:10:36.1066667+00:00

Hi @Richard Keenan,

Your formula syntax is all good and it should work properly now.

If you have any concerns or questions in the future, please don’t hesitate to reach out. Not only will I be happy to assist you, but all moderators in the Q&A forum will also do their best to provide support and guidance.

I wish you a wonderful day ahead.
Richard Keenan 20 Reputation points

2026-05-29T16:47:17.3933333+00:00

OUTSTANDING!!! Tried it again and it worked. Thank you!!!!!!!!

Can I color coat the rows based off of the color of the tabs for the sheets?

Hendrix-C 20,015 Microsoft External Staff Moderator

Hi @Richard Keenan,

Yes, it is possible, but it has to combine with Excel VBA script, not just formula alone. However, my initial formula only stacks the sheets, and it does not return/record any information relates to the rows' source sheet. Therefore, I need to revise the formula to help a source data column to run a VBA script.

The revised formula is:

=LET(
TRWData,HSTACK(TRW!A2:E22,IF(ROW(TRW!A2:A22),"TRW")),
TRFData,HSTACK(TRF!A2:E22,IF(ROW(TRF!A2:A22),"TRF")),
TOSData,HSTACK(TOS!A2:E22,IF(ROW(TOS!A2:A22),"TOS")),
TOEData,HSTACK(TOE!A2:E22,IF(ROW(TOE!A2:A22),"TOE")),
TOJFData,HSTACK(TOJF!A2:E22,IF(ROW(TOJF!A2:A22),"TOJF")),
TOJEData,HSTACK(TOJE!A2:E22,IF(ROW(TOJE!A2:A22),"TOJE")),
TOJSData,HSTACK(TOJS!A2:E22,IF(ROW(TOJS!A2:A22),"TOJS")),
allsheets,VSTACK(TRWData,TRFData,TOSData,TOEData,TOJFData,TOJEData,TOJSData)
duedate,CHOOSECOLS(allsheets,5),
FILTER(allsheets,(duedate>=TODAY())*(duedate<=EDATE(TODAY(),3)),"No matching rows"))

After replacing the old formula, the next step is:

Press Alt + F11 to open VBA Editor
Select Insert > Module > paste this script inside the blank page > close the VBA Editor

Sub ColorDueRowsByOriginalTabColor()
    Dim dueWs As Worksheet
    Dim srcWs As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim srcName As String
    Set dueWs = ThisWorkbook.Worksheets("Due")
    lastRow = dueWs.Cells(dueWs.Rows.Count, "F").End(xlUp).Row
    If lastRow < 14 Then Exit Sub
    dueWs.Range("A14:E1000").Interior.ColorIndex = xlColorIndexNone
    For r = 14 To lastRow
        srcName = Trim(CStr(dueWs.Cells(r, "F").Value))
        If Len(srcName) > 0 And srcName <> "No matching rows" Then
            Set srcWs = Nothing
            On Error Resume Next
            Set srcWs = ThisWorkbook.Worksheets(srcName)
            On Error GoTo 0
            If Not srcWs Is Nothing Then
                If srcWs.Tab.ColorIndex <> xlColorIndexNone Then
                    dueWs.Range("A" & r & ":E" & r).Interior.Color = srcWs.Tab.Color
                End If
            End If
        End If
    Next r
    'Hide the helper source column
    dueWs.Columns("F").Hidden = True
End Sub

User's image

Save the file as Macro-enabled Excel file then press Alt + F8 (or go to Developer tab > Macros) > select the ColorDue macro and press Run

For demonstration, the result will be like this:

User's image

You can try this setup and see if it meets your expectations. If you have any questions, please feel free to ask.

Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-01T22:58:33.6166667+00:00

Good day @Richard Keenan,

Hope things are running smoothly on your end.

Following up on the support thread we've been working on. If you need assistance with anything else, kindly respond to this post, and I will be happy to help.

I want to make sure everything is working as expected and that your experience remains uninterrupted.

Thank you for your patience and understanding throughout the troubleshooting process.

I look forward to hearing from you soon.