How do I write a Google Sheets Query where I can use the user's input (cell reference) to determine the

Question

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?

Answer 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'")