Filter function with more than 1 option same collumn

[fleyd]

Hello,

1.
I have a excel file where i can choose many parameters and filter function will filter results according to those parameters. It was working great until the moment i need to choose for the same parameter, more than one option.

For the parameter "Port" i may or may not have to choose more than one option, but my formula only works for one condition for one parameter, not more. How can i change the formula being able to choose more than 1 "Port" ?

I need the rest of the formula to work, like sorting (end of the formula).

Here is the formula i currently have in B7:

=IFERROR(SORT(FILTER(Jobs!P3:X500; IF(C2="";ROW(Jobs!Q3:Q500);Jobs!Q3:Q500=C2)* IF(D2="";ROW(Jobs!R3:R500);Jobs!R3:R500=D2)* IF(E2="";ROW(Jobs!S3:S500);Jobs!S3:S500=E2)* IF(F2="";ROW(Jobs!T3:T500);Jobs!T3:T500=F2)* IF(G2="";ROW(Jobs!U3:U500);Jobs!U3:U500=G2)* IF(H2="";ROW(Jobs!V3:V500);Jobs!V3:V500=H2)* IF(I2="";ROW(Jobs!W3:W500);Jobs!W3:W500=I2)* IF(J2="";ROW(Jobs!X3:X500);Jobs!X3:X500=J2)); IF(M2="";1;MATCH(M2;B6:J6;0));IF(N2="Asc";1;-1));"Sem Navios")

Believe it's easier with the example in the attached picture.

2.
Also, is there a simple code/Script i can place a folder path in cell A1 (example) and clicking a button, that path will open in windows file explorer? Please consider i'll be using excel for the web, not offline .


For both my questions, please consider i'm using Excel for the Web, not Excel "offline", since the offline i can only use version from 2016, and filter function, or sort, or unique functions...

 

[Fluff]

How about

=IFERROR(SORT(FILTER(Jobs!P3:X500, IF(C2="",ROW(Jobs!Q3:Q500),Jobs!Q3:Q500=C2)* IF(D2="",ROW(Jobs!R3:R500),Jobs!R3:R500=D2)* IF(E2="",ROW(Jobs!S3:S500),Jobs!S3:S500=E2)* IF(F2="",ROW(Jobs!T3:T500),COUNTIFS(F2:F4,Jobs!T3:T500))* IF(G2="",ROW(Jobs!U3:U500),Jobs!U3:U500=G2)* IF(H2="",ROW(Jobs!V3:V500),Jobs!V3:V500=H2)* IF(I2="",ROW(Jobs!W3:W500),Jobs!W3:W500=I2)* IF(J2="",ROW(Jobs!X3:X500),Jobs!X3:X500=J2)), IF(M2="",1,MATCH(M2,B6:J6,0)),IF(N2="Asc",1,-1)),"Sem Navios")

 

[fleyd]

How about

=IFERROR(SORT(FILTER(Jobs!P3:X500, IF(C2="",ROW(Jobs!Q3:Q500),Jobs!Q3:Q500=C2)* IF(D2="",ROW(Jobs!R3:R500),Jobs!R3:R500=D2)* IF(E2="",ROW(Jobs!S3:S500),Jobs!S3:S500=E2)* IF(F2="",ROW(Jobs!T3:T500),COUNTIFS(F2:F4,Jobs!T3:T500))* IF(G2="",ROW(Jobs!U3:U500),Jobs!U3:U500=G2)* IF(H2="",ROW(Jobs!V3:V500),Jobs!V3:V500=H2)* IF(I2="",ROW(Jobs!W3:W500),Jobs!W3:W500=I2)* IF(J2="",ROW(Jobs!X3:X500),Jobs!X3:X500=J2)), IF(M2="",1,MATCH(M2,B6:J6,0)),IF(N2="Asc",1,-1)),"Sem Navios")

Hi,

I tested it briefly and believe it's working. Thanks a lot for the help!!

About my 2º question can you also help? Since i'll be using this in Excel for the web, any ideia on a script i can use to open a folder/path i place for example in cell A1, open it in Windows file explorer?

Many thanks!

 

[Fluff]

I know nothing about xl online scripting language.
I would suggest that you start another thread for that question.

 

[fleyd]

@Fluff , thanks for help on the formula.

I will open another thread for the script question.

 

[Fluff]

You're welcome & thanks for the feedback.