Forum Discussion

Hogstad_Raadgivning's avatar
Hogstad_Raadgivning
Iron Contributor
May 20, 2021
Solved

Filter using dynamic operrator.

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?

 

Here the refference should be T3

 

Best Regards

- Geir

excel
Formulas and Functions
  • JKPieterse's avatar
    JKPieterse
    May 20, 2021
    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.

7 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    May 20, 2021
    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.
    • Hogstad_Raadgivning's avatar
      Hogstad_Raadgivning
      Iron Contributor
      May 20, 2021
      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

Resources