SOLVED

Filter using dynamic operrator.

Steel Contributor

Hi,

 

The goal here is to select the oprator from a list. So the filter function change if the selection is 

<

>

<=

>=

 from cell reference

Function

=FILTER(Tabell1;INDEX(Tabell1;;XMATCH(S4;Tabell1[#Topptekster]))>U4;"")

 

Can the > be a cell refference?

 

filter operand.PNG

Here the refference should be T3

 

Best Regards

- Geir

7 Replies
best response confirmed by Geir Hogstad (Steel Contributor)
Solution
You can do this using a range name. Suppose the > is in C1 and the value in D1, define this range name:
Name: MyFilter
Refersto:
=FILTER(Table1[a],EVALUATE(Table1[a]&Sheet1!$C$1&Sheet1!$D$1))
Now enter =MyFilter somewhere.
Thank you. That looks like a perfect solution. My problem is that I can not figure out what Microsoft har translated EVALUATE into in Norwegian. Could I ask you to please attach a file with =EVALUATE() only, so I can find the formula?

Best regards
- Geir

@Geir Hogstad 

 

Could the LET function be used here?

 

Never used it before..

 

Attached is a samle file.

 

- Geir

@Geir Hogstad Attached.

Perfect, thank you. Translated to "test" even if we do have the word "evaluere" here, Which is the same as evaluate.

_ geir
Nope, Test is the name of the name, you can see what the formula translated to in Name Manager (Formulas tab).
I noticed, when I looked closer.

Thank you again.

Best regards
- Geir
1 best response

Accepted Solutions
best response confirmed by Geir Hogstad (Steel Contributor)
Solution
You can do this using a range name. Suppose the > is in C1 and the value in D1, define this range name:
Name: MyFilter
Refersto:
=FILTER(Table1[a],EVALUATE(Table1[a]&Sheet1!$C$1&Sheet1!$D$1))
Now enter =MyFilter somewhere.

View solution in original post