Filter Formula Function Help

[Arts]

Hi all

I have the following formula (which works fine) this returns the result of what I expect when entering letters within the search cell (Q6).

=FILTER($E$6:$E$11,ISNUMBER(SEARCH(Q6,$E$6:$E$11)),"No result")

My question is it possible to Filter across multiple sheets? At current I have two sheets set up "Current Projects" and "Upcoming Projects" and have the FILTER formula in each sheet rather than having it on one sheet and filtering across them both.

Many thanks
Ahtaf

 

[Fluff]

Ok, how about

=LET(a,'Current Projects'!E6:E11,b,'Upcoming Projects'!E5:E8,ra,ROWS(a),s,SEQUENCE(ra+ROWS(b)),v,IF(s<=ra,a,INDEX(b,s-ra)),FILTER(v,ISNUMBER(SEARCH(Q6,v)),"No result"))

 

[Arts]

Ok, how about

=LET(a,'Current Projects'!E6:E11,b,'Upcoming Projects'!E5:E8,ra,ROWS(a),s,SEQUENCE(ra+ROWS(b)),v,IF(s<=ra,a,INDEX(b,s-ra)),FILTER(v,ISNUMBER(SEARCH(Q6,v)),"No result"))

That is remarkable!! First thoughts are how??? How would you know to do that???

I was really hoping it would be not as complex as that in order to grasp it ie maybe do the FILTER function as I did and then add a + with the second FILTER function.
I mean I usually try and learn what has been provided but that seems to be on a whole new level!

Thank you for helping

 

[Fluff]

Glad we could help & thanks for the feedback.