Dynamic Filter Syntax Assistance

[shellp]

Hello,

I want to create filter formulae dynamically based on values in cells in the worksheet. The formula below works to filter [Fiscal Year] to the value in C4:

Filter(raw_All, Indirect("raw_All[Fiscal Year]")=""&C4)

But what if I want the equal sign (=) to also be dynamic i.e. in B4 where the = or <> would be present in the above formula - what is the syntax? What if I want [Fiscal Year] to be dynamic based on a column name in C5 and how does that impact the formula above?

Any and all assistance greatly appreciated.

 

[6StringJazzer]

If raw_All is a table, and Fiscal Year is a column in that table, you don't need INDIRECT.

If the table and C4 both contain numeric values you do not need to, and should not, append the null string for your comparison.

You cannot make an operator dynamic. However, you can do something like this. In B4 put something like EQUAL or NOT EQUAL then your formula can be

=FILTER(raw_All, (OR(AND(B4="EQUAL",raw_All[Fiscal Year]=C4),AND(B4="NOT EQUAL",raw_All[Fiscal Year]<>C4))))

If you want the column reference to be dynamic then you will need INDIRECT.

=FILTER(raw_All, (OR(AND(B4="EQUAL",INDIRECT("raw_All["&C5&"]")=C4),AND(B4="NOT EQUAL",INDIRECT("raw_All["&C5&"]")<>C4))))

However, I'm not sure what you're trying to do. This last formula still assumes you have a column of years. If you want to check for a match on year on Fiscal Year but return the data from a different column, you need a different formula. But the formula above gives you exactly what you asked for.