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]

What version of Excel are you using? As your profile shows 2016 which doesn't have the Filter function.

Also do you want to stack col E from both sheets & then filter the combined data?

 

[JvdV]

Similar to a question I saw here recently. This might prove helpfull to stack multiple sheets.

I'm thinking something along the lines of:

=LET(x,VSTACK('Current Projects:Upcoming projects'!E6:E11),FILTER(x,ISNUMBER(SEARCH(Q6,x)),"No result"))

 

[Arts]

What version of Excel are you using? As your profile shows 2016 which doesn't have the Filter function.

Also do you want to stack col E from both sheets & then filter the combined data?

Apologies at work I am using OFFICE 365 (home where I am most of the time is 2016)

Indeed what I am hoping to do is stack both columns and then filter the combined data.

 

[Fluff]

In that case how about

=LET(v,VSTACK(Sheet1!$E$6:$E$11,Sheet2!$E$6:$E$11),FILTER(v,ISNUMBER(SEARCH(Q6,v)),"No result"))

 

[Arts]

Sorry for the delay in responding guys, juggling work

Thanks for the replies guys both seem to give me a #name error

@JvdV this maybe because the upcoming projects are not in that range would that matter? I did edit but no luck There are less projects upcoming than there are in the current project list.

Similar to a question I saw here recently. This might prove helpfull to stack multiple sheets.

I'm thinking something along the lines of:

=LET(x,VSTACK('Current Projects:Upcoming projects'!E6:E11),FILTER(x,ISNUMBER(SEARCH(Q6,x)),"No result"))

In that case how about
=LET(v,VSTACK(Sheet1!$E$6:$E$11,Sheet2!$E$6:$E$11),FILTER(v,ISNUMBER(SEARCH(Q6,v)),"No result"))

@Fluff I did try to change the name of sheets but this gave me an error as well

Screen shot of formula inserted into file below (@Fluff I did try to edit the names of the sheets but had to undo as this gave an error)

I am very unfamiliar with the formula provided so pasted as it was, wasnt sure if I needed to edit anything else. I notice there is an x with the formula provided by JVDV and then a V with the one provided by Fluff

 

[Fluff]

If you type =cho into a cell how many functions appear in the auto completion list?

 

[Arts]

If you type =cho into a cell how many functions appear in the auto completion list?

Just the one: CHOOSE

 

[Fluff]

Ok, you don't have the new functions yet. It would be worth running an update, but in the mean time what are the two sheet names & ranges?

 

[Arts]

Ok, you don't have the new functions yet. It would be worth running an update, but in the mean time what are the two sheet names & ranges?

Current Projects E5:E11 (Header Included)
Upcoming Projects E4:E8 (Header Included)