-1

Below is my query function in its original form: =ArrayFormula(query({importrange("1yqTUmJcL6YxgOpfHS5Pt9nYnmpiqN3tUPQP7-Rp8xis","CPG!A2:L20");importrange("1yqTUmJcL6YxgOpfHS5Pt9nYnmpiqN3tUPQP7-Rp8xis","PUB!A2:L20")},"Select Col5, Sum (Col4) where Col6='' group by Col5 pivot Col7"))

Which I am trying to shorten the formula by using indirect to refer to concatenated import ranges by the following attempt

=ArrayFormula(query(indirect("JOIN!J3"),"Select Col5, Sum (Col4) where Col6='' group by Col5 pivot Col7"))

but come up with this error:

Error Unable to parse query string for Function QUERY parameter 2: NO COLUMN: Col5

The Join!J3 cell contains the value below:

{importrange("1yqTUmJcL6YxgOpfHS5Pt9nYnmpiqN3tUPQP7-Rp8xis","CPG!A2:L20");importrange("1yqTUmJcL6YxgOpfHS5Pt9nYnmpiqN3tUPQP7-Rp8xis","PUB!A2:L20")}

I research various similar question in forums but their answers comes to no solution. Usually it is to use "Select Col1" instead of "Select A" but my formulas are all using Col1, Col2 already. Am doing anything wrong?

I have included some images for further clarification.

The original formula: enter image description here

The shortening attempt: enter image description here

The cell value in sheetname: JOIN cell: J3: enter image description here

The cell value in J3 is actually a pasted value from cell A1: enter image description here

cokenol
  • 19
  • 7
  • Unfortunately, you cannot call **importRange** from inside **indirect**. **Indirect** requires a string address pointing inside the current spreadsheet. "Sheet Name!A1:D4" for example. – CalamitousCode Dec 18 '18 at 11:01
  • Okay understood. It has to be the string address within the current spreadsheet then. Thanks. – cokenol Dec 19 '18 at 01:20
  • Is there any alternative towards this? cuz all the data I am trying to query is actually in the same workbook, just in different sheetnames. – cokenol Dec 19 '18 at 01:27
  • I have edited my answer. It will help you shorten your formula considerably. – CalamitousCode Dec 19 '18 at 01:27

1 Answers1

0

If I understand correctly, because your formula is now referring to a specific range in the sheet instead of an array of ranges, you will need to use A, B instead of Col1, Col2, etc.

I assume the data produced by cell J3 in the sheet JOIN displays data in J3:U21 (based on the range A2:J20). You need to query all of this, not just the cell containing the formula.

Try the formula below. (I have made assumptions about what columns the data sits in--please amend where necessary).

=ArrayFormula(query(indirect("JOIN!J3:U21"),"Select N, Sum (M) where O='' group by N pivot P")

EDIT: You seem to be using the same range and ID. You can refer to them using just two cells.

={IMPORTRANGE(D6,"SheetName1"&E6);IMPORTRANGE(D6,"SheetName2"&E6)}

enter image description here

CalamitousCode
  • 1,324
  • 1
  • 14
  • 21