0

I am trying to create a query to pull ALL reports available in the referenced table but then limit down the reports when options are selected.

The formula I have so far is:

=QUERY('Common Standard Reports & Fields Included'!A:AG, "SELECT A WHERE
"&IF(COLUMN(B6)>=1,SUBSTITUTE(ADDRESS(1,match(B6,'Common Standard Reports & Fields Included'!$1:$1,0),4),"1",""),"")&" contains 'Yes' AND "&SUBSTITUTE(ADDRESS(1,match(B7,'Common Standard Reports & Fields Included'!$1:$1,0),4),"1","")&" contains 'Yes' ")

If options are selected in the referenced fields, B6 and B7 (and I plan to add several more), then the query works great. However, I don't want to require that the fields be used, and when the field is blank, then the query should skip over this and not use it as a "WHERE" condition.

This is the final step in my sheet, so I would be SOOO grateful for any help!!!

See screenshots: enter image description here

enter image description here

0

I figured it out! I had to create a "Default" column in the referenced tables, and then set the blank fields to the default of Yes.

=QUERY('Standard Report Tables'!A:CC, "SELECT A WHERE "&IF(ISBLANK(B6)=FALSE,SUBSTITUTE(ADDRESS(1,match(B6,'Standard Report Tables'!$1:$1,0),4),"1",""), "B")&" contains 'Yes' AND "&IF(ISBLANK(B7)=FALSE,SUBSTITUTE(ADDRESS(1,match(B7,'Standard Report Tables'!$1:$1,0),4),"1",""), "B")&" contains 'Yes' AND "&IF(ISBLANK(B8)=FALSE,SUBSTITUTE(ADDRESS(1,match(B8,'Standard Report Tables'!$1:$1,0),4),"1",""), "B")&" contains 'Yes' AND "&IF(ISBLANK(B9)=FALSE,SUBSTITUTE(ADDRESS(1,match(B9,'Standard Report Tables'!$1:$1,0),4),"1",""), "B")&" contains 'Yes' AND "&IF(ISBLANK(B10)=FALSE,SUBSTITUTE(ADDRESS(1,match(B10,'Standard Report Tables'!$1:$1,0),4),"1",""), "B")&" contains 'Yes' AND "&IF(ISBLANK(B14)=FALSE,SUBSTITUTE(ADDRESS(1,match(B14,'Standard Report Tables'!$1:$1,0),4),"1",""), "B")&" contains 'Yes' AND "&IF(ISBLANK(B15)=FALSE,SUBSTITUTE(ADDRESS(1,match(B15,'Standard Report Tables'!$1:$1,0),4),"1",""), "B")&" contains 'Yes' AND "&IF(ISBLANK(B16)=FALSE,SUBSTITUTE(ADDRESS(1,match(B16,'Standard Report Tables'!$1:$1,0),4),"1",""), "B")&" contains 'Yes' AND "&IF(ISBLANK(B20)=FALSE,SUBSTITUTE(ADDRESS(1,match(B20,'Standard Report Tables'!$1:$1,0),4),"1",""), "B")&" contains 'Yes' ")

-1

Maybe you can try using the Filter function instead?

=FILTER(range, condition1, condition2...)

2
  • I am not super familiar with the FILTER function, but no matter what I do, I keep getting the following error: FILTER has mismatched range sizes. Expected row count: 968. column count: 1. Actual row count: 1, column count 1. What does that error mean? Oct 11 at 20:43
  • @DavidHammond Each parameter should have the same number of rows. Usually this happens when comparing a column with a single value. In this case the fix is simple, use ArrayFormula.
    – Rubén
    Oct 12 at 12:32

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.