1

The goal of this sheet I am working on is for someone to select the items they want on a report (I created a dropdown with data validation), and based on the options the user select, THIS will be the Column letter to use in my Query.

For example, my query works perfectly when I do:

=QUERY('Common Standard Reports & Fields Included'!A:AG, "select A WHERE C='Yes'")

However, the "C = 'Yes' part is where I'm struggling. I don't want the user to have to edit my formula. I want to use the column number from what they selected.

I created the following formula to pull in the letter of the answer, based on the data tables:
=SUBSTITUTE(ADDRESS(1,match(B6,'Common Standard Reports & Fields Included'!$1:$1,0),4),"1","")

This works great all on its own, but when I try to do something like this, it doesn't work:

=QUERY('Common Standard Reports & Fields Included'!A:AG, "select A WHERE '"&SUBSTITUTE(ADDRESS(1,match(B6,'Common Standard Reports & Fields Included'!$1:$1,0),4),"1","")&"' = 'Yes'")

Basically, this works:
=QUERY('Common Standard Reports & Fields Included'!A:AG, "select A WHERE C='Yes'")

But the one above doesn't.

Can anyone help me figure out what I'm doing wrong here?

1 Answer 1

1

It looks that the problem is that your formula is enclosing the columns letters between ', in other words, remove the first ' after WHERE and the one before =:

=QUERY('Common Standard Reports & Fields Included'!A:AG, "select A WHERE '"&SUBSTITUTE(ADDRESS(1,match(B6,'Common Standard Reports & Fields Included'!$1:$1,0),4),"1","")&"' = 'Yes'")
                                                                         ^                                                                                                 ^
                                                                         |                                                                                                 |
                                                                         |                                                                                                 |

The resulting formula is:

=QUERY('Common Standard Reports & Fields Included'!A:AG, "select A WHERE "&SUBSTITUTE(ADDRESS(1,match(B6,'Common Standard Reports & Fields Included'!$1:$1,0),4),"1","")&" = 'Yes'")

2
  • Ruben, THANK YOU so much! I was reading online about the syntax for this, and so many places I was reading showed both a ‘ and a “ but I tried what you said and it worked!!!! You’re my hero! Oct 9, 2021 at 13:15
  • 1
    @DavidHammond Please read What should I do when someone answers my question
    – Rubén
    Oct 12, 2021 at 12:34

Your Answer

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

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