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

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.