Custom Filtering Data

[Justin_A]

I have a time tracking app that my employees use. The main timesheet spreadsheet has columns such as name, date, service item, etc.

I have set up a separate spreadsheet that has only columns I wish to filter. This sheet has only 1 row of data. I can edit this row through a detail view.  The inputs in this detail tell the timesheet slice how to filter the data.  The filtering of the data sort of works but I am having trouble getting it to work how I'd like.

Currently, I use the IN() function to slice the data. I have IN() statements for each column I wish to filter. The problem is simultaneously filtering by multiple columns. I have not figured out the correct mix of IF AND OR statements to have this function correctly.

I am currently using AND with IN and the data only filters if I have a every filter column filled in.

What I really want is to filter by ONLY the filter columns that are filled in and ignore blank ones. 

How can i incorporate IF into my slice formula to achieve this result?

Here is a sample of by current formula:

AND(IN([approved status],Time Filter[approved status]),IN([name],Time Filter[name]),IN([company],Time Filter[company]),IN([service item],Time Filter[service item]))

[Marc_Dillon · Accepted solution]

AND(
  OR(
    IN(...) ,
    ISBLANK(...)
  ) ,
  ...
)

[Justin_A]

Thank you! That works perfectly.

[ADAJALAN]

Can u share that formula ??