SOLVED

Filter using dynamic operrator.

%3CLINGO-SUB%20id%3D%22lingo-sub-2371332%22%20slang%3D%22en-US%22%3EFilter%20using%20dynamic%20operrator.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2371332%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20goal%20here%20is%20to%20select%20the%20oprator%20from%20a%20list.%20So%20the%20filter%20function%20change%20if%20the%20selection%20is%26nbsp%3B%3C%2FP%3E%3CP%3E%26lt%3B%3C%2FP%3E%3CP%3E%26gt%3B%3C%2FP%3E%3CP%3E%26lt%3B%3D%3C%2FP%3E%3CP%3E%26gt%3B%3D%3C%2FP%3E%3CP%3E%26nbsp%3Bfrom%20cell%20reference%3C%2FP%3E%3CP%3EFunction%3C%2FP%3E%3CP%3E%3DFILTER(Tabell1%3BINDEX(Tabell1%3B%3BXMATCH(S4%3BTabell1%5B%23Topptekster%5D))%3CSTRONG%3E%26gt%3B%3C%2FSTRONG%3EU4%3B%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20the%20%26gt%3B%20be%20a%20cell%20refference%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22filter%20operand.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282121i40939A34F6293D25%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22filter%20operand.PNG%22%20alt%3D%22filter%20operand.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHere%20the%20refference%20should%20be%20T3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2371332%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2371569%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20using%20dynamic%20operrator.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2371569%22%20slang%3D%22en-US%22%3EYou%20can%20do%20this%20using%20a%20range%20name.%20Suppose%20the%20%26gt%3B%20is%20in%20C1%20and%20the%20value%20in%20D1%2C%20define%20this%20range%20name%3A%3CBR%20%2F%3EName%3A%20MyFilter%3CBR%20%2F%3ERefersto%3A%3CBR%20%2F%3E%3DFILTER(Table1%5Ba%5D%2CEVALUATE(Table1%5Ba%5D%26amp%3BSheet1!%24C%241%26amp%3BSheet1!%24D%241))%3CBR%20%2F%3ENow%20enter%20%3DMyFilter%20somewhere.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2372206%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20using%20dynamic%20operrator.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2372206%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20the%20LET%20function%20be%20used%20here%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENever%20used%20it%20before..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20a%20samle%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2372355%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20using%20dynamic%20operrator.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2372355%22%20slang%3D%22en-US%22%3EPerfect%2C%20thank%20you.%20Translated%20to%20%22test%22%20even%20if%20we%20do%20have%20the%20word%20%22evaluere%22%20here%2C%20Which%20is%20the%20same%20as%20evaluate.%3CBR%20%2F%3E%3CBR%20%2F%3E_%20geir%3C%2FLINGO-BODY%3E
Regular 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 (Regular 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