=filter('CASES LIST'!C:S,'CASES LIST'!Q:Q="admission",len('CASES LIST'!S:S))

I have in the sheet column Q - has "admission", "consultation", other items in dropdown list.

In column S- other dropdown list as well.

In column R- drop down list

I want to filter data based on either of these two criteria:

1- Column Q="admission" AND column S is not empty

2- OR Column Q= "consultation"

If either of these criteria is met, the row C:S, will be copied to the desired sheet

I made this formula, but it doesn't seem working

=filter('CASES LIST'!C:S,'CASES LIST'!Q:Q="admission",len('CASES LIST'!S:S))+('CASES LIST'!Q:Q="consultation")

Please use the following query formula (adjusting ranges to your needs), instead of the filter you already use.

=QUERY({P1:S},"where Col2='admission' AND Col4<>'' OR Col2='consultation' ",0)

enter image description here

Functions used:

  • 1
    Have you tried the given solution? Did it work for you? If yes, as per site guidelines when an answer addresses your question, you should accept it and maybe upvote it so others can benefit as well. If not, please read how to and share a test sheet so as you can be easier helped. – marikamitsos Jan 6 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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