Filter multiple options in return

[Montanes]

Sheet1 has a list of vehicles that are booked for maintenance as follows:

Week Number	Date	Vehicle	Maintenance	Comments
42	14/10/2024	ABC1	Inspection	Drop off early
42	15/10/2024	ABC3	Service	Nightshift
44	01/11/2024	XYZ20	MOT	Afternoon

Sheet2 needs to draw the relative information from sheet1 for the week as specified in cell E1 (in this case it would be week 42)

Date	Vehicle	Maintenance	Comments
14/10/2024	ABC1	Inspection	Drop off early
15/10/2024	ABC3	Service	Nightshift

Sheet3 is the same as Sheet2 but "E1" would have a month (can format as "October" or "10") instead of a week number and hopefully return the data for that whole month instead of a single week.

I hope this makes sense, i am using someone elses computer so i am unable to upload a sample just now.

Thank-you in advance for you assistance.

 

[Joe4]

Welcome to the Board!

Since you are using Excel 365, you can use the new FILTER function in order to do this.

So, if this is how the data appears on Sheet1:

We can put the following formula in cell A4 on Sheet2 like this to return what you want:

=FILTER(Sheet1!B2:E4,Sheet1!A2:A4=Sheet2!E1,"")

And for Sheet3, it would be simplest to add Month Start Date in cell E1 and Month End Date in cell F1, and use this formula in cell A4:

=FILTER(Sheet1!B2:E4,(Sheet1!B2:B4>=Sheet3!E1)*(Sheet1!B2:B4<=Sheet3!F1),"")

 

[Montanes]

Thanks very much for this, the solution for Sheet2 has worked a treat, however Sheet3 is missing a bit.

Unlike sheet2, sheet3 has only auto-populated the solution in column A so there is no return value in columns B, C or D.

 

[Joe4]

Thanks very much for this, the solution for Sheet2 has worked a treat, however Sheet3 is missing a bit.

Unlike sheet2, sheet3 has only auto-populated the solution in column A so there is no return value in columns B, C or D.

Sounds like you mis-typed the formula.
Check it again, and if it is still not working, please the exact formula you are using.

 

[Montanes]

I had indeed mis-typed the formula, thanks.

 

[Joe4]

You are welcome.
Glad I was able to help!