2

I am trying to get the Query to pull from the Outside Sheets range but have the Where look at the number in a cell on a tab within the current sheets. I have reviewed other questions and none of those solutins ahs worked for this.

I want this so that I dont have to change the number in each query for every sheets created. We want use a template with all the formulas and then copy and adjust just one cell and that way the correct data is being pulled for each instance.

Current: =QUERY({IMPORTRANGE("URL", "JAN!A2:T"),"SELECT Col11, Col12, Col13, Col14, Col15, Col16 WHERE Col1 = 804793 ,0)"

Trying but getting a parrsing error: =QUERY({IMPORTRANGE("URL", "JAN!A2:T"),"SELECT Col11, Col12, Col13, Col14, Col15, Col16 WHERE Col1 = "&Overview!B2,0)

We have almost 300 of these to creat and we would have to change the number in each QUERY for each monthly tab if we can't get it to just reference the number in the current sheets overview tab.

Open to using App Scripts if it makes it easier just not great with the language.

player0
  • 124,011
  • 12
  • 67
  • 124
Toni4tonight
  • 33
  • 2

1 Answers1

1

try:

=QUERY({IMPORTRANGE("URL"; "JAN!A2:T")};
 "select Col11,Col12,Col13,Col14,Col15,Col16 
  where Col1 = "&Overview!B2*1; 0)

or:

=QUERY({IMPORTRANGE("URL"; "JAN!A2:T")};
 "selece Col11,Col12,Col13,Col14,Col15,Col16 
  where Col1 = '"&Overview!B2&"'"; 0)
player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    You are amazing and I feel dumb lol Thank you! – Toni4tonight Jan 11 '23 at 02:12
  • Do you know if there is a way to make the sheet is calling dynamic as well? Like `=QUERY({IMPORTRANGE("URL"; **"JAN!A2:T"**)}; "SELECT Col11,Col12,Col13,Col14,Col15,Col16 WHERE Col1 = '"&Overview!B2&"'"; 0)` changes to `=QUERY({IMPORTRANGE("URL"; ** "FEB!A2:T" **)}; "SELECT Col11,Col12,Col13,Col14,Col15,Col16 WHERE Col1 = '"&Overview!B2&"'"; 0)` if the tab/sheet it is imported on is different? Like a MACRO or AppScrpit/JavaScript that can change the sheet being used? – Toni4tonight Jan 11 '23 at 02:30
  • @Toni4tonight well instead of `"JAN!A2:T"` you could have `A1&"!A2:T"` where A1 will be dropdown with `JAN,FEB,etc`. – player0 Jan 11 '23 at 02:38
  • So the only issue is that it's 2 different tabs/sheets in each sheets doc. So like there is the source doc that has Jan-Dec tabs and then there are multiple target docs that have an overview tab and Jan-Dec tabs. This current fix to my main problem does massively help as I can technically just go into each target tab and change the query to the corresponding source tab. And currently, that'd be less time than trying to find a different way but Id like another way incase we add more tabs or something. – Toni4tonight Jan 11 '23 at 05:02
  • Okay I found a sort of work around not sure it is much better BUT now the question is "Is there a way to refer to a sheet name in the imported sheet doc vs a cell?" – Toni4tonight Jan 11 '23 at 06:26