yogi_Set Up QURY Formulation For Specified Criteria Involving Some Or All

                                           Google Spreadsheet   Post  #1955
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 13, 2015

post by Shashi Krishna:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/uXx8loY3tcQ

And/Or in an =QUERY condition question

Hey guys,

So I have a Sheet with 2 sheets in it. Sheet 1 has the following type of data:

------------------------------------------------------------------------------------------------------------------------------

NAME    ABSENCE TYPE        DATE FROM                 DATE TO

------------------------------------------------------------------------------------------------------------------------------

abc            Sick Leave                 01-05-2015                 03-05-2015

def             Personal Leave          02-04-2015                 04-04-2015

abc            Personal Leave          06-04-2015                 07-04-2015

...

etc.

On Sheet 2 I have the following formula going.....

=query({importRange("documentID","Sheet1!A2:N")},"select Col1,Col2,Col3,Col5,Col6,Col7,Col8,Col13 where Col2 <> '' and (Col3="&  "'" & H6 & "' or Col2="&  "'" & I6 & "')and Col11='Approved' and toDate(Col5)>=date '"& TEXT( E6 , "yyyy-MM-dd" ) & "' and toDate(Col5) <= date '"& TEXT( G6 , "yyyy-MM-dd" ) & "' " , 0 )

H6 in Sheet2 is the Absence Type and I6 in Sheet2 is Name of the person. 

How do I make the condition such that...

1. If name is provided then ALL absences of that person are shown.

2, If absence type AND name is provided then THAT SPECIFIC ABSENCE TYPE for THAT SPECIFIC USER is shown.

Since I am using an OR inside an AND...it works partially. Can someone please help?

Thanks.

Shashi

--------------------------------------------------------------------------------------