-1

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

Improve this question
1

2 Answers 2

Reset to default
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' ")

Improve this answer
-1

Maybe you can try using the Filter function instead?

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

Improve this answer
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?
    – David Hammond
    Commented Oct 11, 2021 at 20:43
  • 1
    @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 - Volunteer Moderator - ♦
    Commented Oct 12, 2021 at 12:32

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.