1

First time ever using stack exchange so please excuse any formatting problems.

I'm trying to create a sheet where Sheet1 is a dump of data and Sheet2 contains:

  • A date range
  • A list of items
  • True/False checkboxes for each item

I pretty much immediately wrote off the checkboxes as helper cells and created a hidden query to dump everything that was marked true in what I'm going to call Range_Items (Though I'm really just referencing the cells they were dumped in).

I haven't tried limiting the final query against the date range yet, because I'm having trouble making it pull from Sheet1 against the Range_Items list. So I tried paring it down to a simpler formula for concept and still can't seem to figure out why it won't pull anything. This is the simplest iteration I can think of:

=Query('Transaction Dump'!$A:$J,"select B,C,E,F,G,I where C = 'Expense'")

But when I try C = Or('Expense', 'Transfer') using cell reference or lack of single quotes, I get hit with errors.

I'm sure the problem is the syntax, but I really can't tell what's stopping it.

Some other attempts I've tried include:

  • Simply referencing the cell range in the OR function
  • Nesting another query into the query
  • =TEXTJOIN(",C =",True,$O$41:$O$57) where I then prefix that statement with =arrayformula("C = " & $C$59) since the first list item would not have the C = operator and stick that into the Query's Or function.

I've tried a billion combinations of single, double, and & insertions. Any help would be greatly appreciated.

Improve this question

1 Answer 1

Reset to default
1
  • try it like this and if you will need in future the and you just replace the or: 
    =QUERY('Transaction Dump'!$A:$J, 
 "select B,C,E,F,G,I 
  where C = 'Expense'
     or C = 'Transfer'", 0)
  • also you may change = to matches or contains

_______________________________________________________________

cell B59: 

="contains '"&TEXTJOIN("' or Col3 contains '", 1, $O$41:$O$57)&"'"

cell Y3: 

=QUERY({'Transaction Dump'!$A7:$J}, 
 "select Col2,Col3,Col5,Col6,Col7,Col9 where Col3 "&C59, 0)

0

Improve this answer
12
  • Changing = to matches and swapping and for or seems to be doing the trick for manual insertion. But I'm still trying to reach the goal of it pulling it from that Range_List of items as I'll be checking them on or off whether I need that data at the moment. I thought if I populated a cell with a string of C matches ___ then I could just insert that cell reference into the query, but that doesn't seem to work. Also, I may not know the proper way to reference a cell in a Query function. I'm inserting it 'C60'.
    – Ryan Martinez
    Commented May 15, 2019 at 14:13
  • that won't do coz QUERY is not able to work with arrays/ranges mentioned as comparison params
    – user0
    Commented May 15, 2019 at 14:19
  • What would you suggest as an alternative? I was tooling around with the filter function, but that doesn't seem to be working as intended either. Seems like it needs a very finite range, because attempting an entire column gives me a "mismatched range" error.
    – Ryan Martinez
    Commented May 15, 2019 at 17:45
  • can you share a copy of your sheet?
    – user0
    Commented May 15, 2019 at 18:11
  • Sure, here's a link of a copy: docs.google.com/spreadsheets/d/… The data segregation sheet was a totally unnecessary step I took that is not utilized. I'm just leaving it there in case I do think of a way to use it in that format.
    – Ryan Martinez
    Commented May 15, 2019 at 18:16

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.