Filter formula based on date range and start of Ref?

[Add365]

Hi Everyone,

Can you help me with a problem Im having with my formula. I have 3 tabs of data labelled April 2022, May 2022, June 2022, all the cells and data in these tabs are in the same positions.

I am trying to create a filter formula on a new sheet to filter between 2 date ranges which I type into 2 cells & filter down the refrence which I also type into a search box.

At the moment I am only able to filter 1 tab between 2 date ranges and search for a exact refence using the below formula but I want it to search for just the first 3 letters of the refrence and it to search through all 3 tabs.

=FILTER('June 2022'!A2:K2000,('June 2022'!A2:A2000>=H2)*('June 2022'!A2:A2000<=I2)*('June 2022'!B2:B2000=G2))

I have tried the below formula to make it search through all 3 tabs aswell but it keeps coming up CALC

=FILTER('April 2022:June 2022'!A2:K2000,('April 2022:[June 2022]June 2022'!A2:A2000>=H2)*('April 2022:[June 2022]June 2022'!A2:A2000<=I2)*('April 2022:[June 2022]June 2022'!B2:B2000=G2))

I have also tried the below formula which works to filter the refrences down by the first 3 letters but I cant get this to work with the date range aswell.

=FILTER('June 2022'!A2:K2000,ISNUMBER(SEARCH('April,May,June'!G2,'June 2022'!B2:B2000)), "No Results")

Can anyone help me please.

Thanks

 

[Fluff]

You cannot use 3D references in the Filter function, have a look here to see how you can filter multiple ranges Summarize Multiple Tables Into One With Filter

 

[Add365]

Ok thanks I have had a look and Im going to just filter it Monthly (1 Tab), Im am going to use the formula below, is there a way to change this formula so it searches for the first 3 letters of the refrence instead of it having to be an exact match?

=FILTER('June 2022'!A2:K2000,('June 2022'!A2:A2000>=H2)*('June 2022'!A2:A2000<=I2)*('June 2022'!B2:B2000=G2))

 

[Fluff]

Do you want to check the 1st 3 characters of col B & match them to G2?

 

[Add365]

Yes
The refrences can be ICE202206001209, ECE202206001209, KCE202206001209 and so on so I want to be able to type ICE or ECE into cell G2 and it will filter them all while including the date range formula at the start.

 

[Fluff]

Ok, how about

=FILTER('June 2022'!A2:K2000,('June 2022'!A2:A2000>=H2)*('June 2022'!A2:A2000<=I2)*(LEFT('June 2022'!B2:B2000,3)=G2))

 

[Add365]

Yes thats it thanks I knew I was missing something at the end of the formula but couldnt work it out. Can I ask if I only wanted to see columns 1,2,3,4,5,6,8,9,11 but not columns 7&10 what code would I add in and where? I know its 0 and 1's but not sure where to add it on the formula?

 

[Fluff]

How about

=LET(f,FILTER('June 2022'!A2:K2000,('June 2022'!A2:A2000>=H2)*('June 2022'!A2:A2000<=I2)*(LEFT('June 2022'!B2:B2000,3)=G2)),INDEX(f,SEQUENCE(ROWS(f)),{1,2,3,4,5,6,8,9,11}))

 

[Add365]

ah yes that has worked I didnt have the LET part or the SEQUENCE in my formula when I tried to do it hence why it wouldnt work. Thanks for your help with this much appreciated

 

[Fluff]

You're welcome & thanks for the feedback.