yogi_Using INDIRECT Function With Multi Sheet QUERY

Google Spreadsheet   Post  #2067

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-05-2016

question by: Elden Fenison:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/klwaiOAhKNU;context-place=forum/docs

Using the indirect function with multi-sheet query, syntax help
I have the following formula that works fine. It queries a handful of budget sheets looking for expenditures in a certain category.

=sort(query({Amazon!A2:F;Amex!A2:F;'Cash+'!A2:F;'Double Cash'!A2:F;'USB Checking'!A2:F},"Select Col1,Col2,Col6 where Col5 ='"&D1&"'"))

I would like to use the indirect function to refer to these sheets instead of spelling them out. I have cells on this sheet that contain the names of the other sheets that I want to query.

I just can't seem to get the syntax right.

I've tried this... and it doesn't work.

=sort(query(indirect(E2&”!A2:F”);indirect(E3&”!A2:F”);indirect(E4&”!A2:F”);indirect(E5&”!A2:F”);indirect(E6&”!A2:F”);indirect(E7&”!A2:F”),"Select Col1,Col2,Col6 where Col5 ='"&D1&"'"))

The sheets names I want to query are in cells E2:E7.

------------------------------------------------------------------------------------------------------------------------------