0

I have this formula to combine and filter multiple sheets if a certain column has "Y" in it:

=SORT({filter(Bryson!A4:S502, REGEXMATCH(Bryson!Q4:Q502, "Y"));filter(David!A4:S502, REGEXMATCH(David!Q4:Q502, "Y"));filter(Justin!A4:S502, REGEXMATCH(Justin!Q4:Q502, "Y"))},18,1)

Multiple people work within their named sheets, 9 total, and data is wiped weekly. Problem is, when somebody has zero hits for the filter, the entire equation errors with VALUE#. How do I make it ignore arguments when there is no matches?

1
  • Welcome to Web Applications Stack Exchange. Google Sheets and Microsoft have different feature sets and require different answers. Your formula looks like its from Google Sheets so I am assuming that platform. Edited that tags accordingly. Commented Dec 16, 2022 at 20:24

1 Answer 1

1

Combine the ranges "the other way":

=sort( 
  filter( 
    { Bryson!A4:S; David!A4:S; Justin!A4:S },
    regexmatch({ Bryson!Q4:Q; David!Q4:Q; Justin!Q4:Q }, "Y") 
  ), 
  18, 1 
)

Alternatively, use query():

=query( 
  { Bryson!A4:S; David!A4:S; Justin!A4:S },
  "where Col17 contains 'Y' order by Col18", 0 
)
1
  • This works, thank you! I'm using filter instead of query to maintain a hyperlink unique to each row Commented Dec 16, 2022 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.