0

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 Ys 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.

Improve this question
11
  • 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
  • 1
    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
  • 1
    The formulas have syntax errors, more specifically the " are wrong.
    – Rubén ♦
    Nov 28, 2020 at 22:02
  • 1
    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
  • 1
    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

1 Answer 1

Reset to default
1

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

Improve this answer
5
  • 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.
    – sidthesloth
    Nov 28, 2020 at 19:09
  • 1
    @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'.
    – sidthesloth
    Nov 28, 2020 at 19:52
  • 1
    Please add tha to the question, also create couple of demo spreadsheets, one representing SheetA and the other representin the spreadsheet where you are adding the IMPORTRANGE formula, share them with anyone with the link to view only, then add the link to second spreadsheet to your question.
    – Rubén ♦
    Nov 28, 2020 at 20:24
  • I added the sheet links, and provided more details on what I need. Thank you for the instructions. Looking forward to the responses.
    – sidthesloth
    Nov 28, 2020 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.