importrange & query

I don't have programming background, but as a novice, experiment with formulas on my own.

I have two separate live sheets;

On the 1st sheet (sheet A) tab F, when I write `Y` into any cell in Column O, say `O6`, I need the data in the next column(Q) same row (6): `P6`, to be transferred (dynamically -thus assumed the formula as indirect- updated for each row) into sheetB column D. Since these are live separate sheets I know that I need to use `importrange` but dont know how to write the formula.

``````=IMPORTRANGE("SheetA";indirect("F!P")& IF(P6:P:275, "Y"))
``````

Update #1: Did some reading and research, and found out `query` function might work better, thus have come up with the formulas below. However, while the first currently gives me `formula parse error`, the second gives `value!` warning.

``````=QUERY(IMPORTRANGE("SheetA","F!O:P"), “Select #(Col2) where Y N(Col1) contains ‘Y’ “)

=QUERY(IMPORTRANGE("SheetA","F!O:P"), "Select * Where Col2=<>"")")
``````

Update #2: here are the sheets, should anyone want to take a look; Demo 1 Demo 2

I need the data on Demo 1, `P7`, to automatically be added to Demo 2, `D11`. Moreover, if possible, I need this formula cover all the rows (+to be added) on Demo 1 sheet, while all, say 25 occurrences out of 350, are added under each on Demo 2, without moving the whole list from Demo 1.

My starting point was to tag the `Y`s with an `if` formula that created the number in `P7`. That's why on Demo 1, `N` creates `FALSE`, whereas `Y` `1010101010`. However, if there is any other option/formula, all fine by me. Otherwise, manual calculation takes too much time.

• Welcome to Web Applications. Insted of INDIRECT you should do a concatenation. The formula included has multiple syntax errors.Try to write each part on separate cells to fix the problems of each of them first, then put them together.
– Rubén
Nov 27, 2020 at 21:40
• I can't find any formula on Demo2. I suggest you to add to it one of the formulas that you tried.
– Rubén
Nov 28, 2020 at 21:20
• The formulas have syntax errors, more specifically the `"` are wrong.
– Rubén
Nov 28, 2020 at 22:02
• TIP: `"` should not be used inside of the second argument oF QUERY, i.e. instead of `"Y"` you should use `'Y'`, second tip, instead of `Col2=<>""` use `Col2=<>''` or `Col2 is not null`
– Rubén
Nov 28, 2020 at 22:05
• Also some formulas have `)5` this is wrong but I don't know what are you trying to do with that.
– Rubén
Nov 28, 2020 at 22:08

Regarding

``````=QUERY(IMPORTRANGE("SheetA","F!O:P"), “Select #(Col2) where Y N(Col1) contains ‘Y’ “)
``````

one problem is that it's using typographical quotes instead of straight quotes. As second problem is that the second argument syntax is wrong.

Regarding

``````=QUERY(IMPORTRANGE("SheetA","F!O:P"), "Select * Where Col2=<>"")")
``````

IMPORTRANGE

It's very likely that it's returning an error because the first parameter of `IMPORTRANGE` isn't a valid URL or spresdsheet key. It also might be caused b/c the spreadsheet was not authorized to access SheetA. Please take a look to the related questions for details on how to solve this problems.

QUERY

The second argument of QUERY has syntax errors. Instead of `Col2=<>""` use `Col2=<>''` or `Col2 is not null`

Related

• thank you for your response, and keen eyes. I corrected the quotes and double checked to make sure the authorization is active, and it is. No change so far. Nov 28, 2020 at 19:09
• @sidthesloth Besides the quotes, the formula has another problem, the syntax is wrong. What are you trying to do in that formula? What about the second formula?
– Rubén
Nov 28, 2020 at 19:15
• I wrote both formulas for the same purpose and failed so far; from the SheetA, I need the number on the right in the column P, say '12345', to be transferred whenever the formula encounters the 'Y' in a cell in the column O, and this should apply to and cover the both columns O and P. (Would probably end up with around 25 cases max (out of 350 data rows), had done them manually so far) On a side note, the sheet has 5 rows of header, just included that in the formula as well, still receiving 'formula parse error'. Nov 28, 2020 at 19:52