1

I need to import data from "Spreadsheet A" to "Spreadsheet B" but all the data is in two separate sheets "MY" and "NY" in "Spreadsheet A"

I basically need exactly this but with a function to import from another spreadsheet, not only within the same one

=QUERY({{ARRAYFORMULA(ROW(INDIRECT("A1:A"&COUNTA(QUERY(MY!A2:A; "select A where A is not null")))))\QUERY(MY!A2:A; "select A where A is not null")};  {ARRAYFORMULA(ROW(INDIRECT("A1:A"&COUNTA(QUERY(NY!A2:A; "select A where A is not null")))))\QUERY(NY!A2:A; "select A where A is not null")}}; "select Col2 order by Col1")

1 Answer 1

1
  • first, run one IMPORTRRANGE to connect the sheets:

    =IMPORTRANGE("ID"; "MY!A2")

  • then try:

    =QUERY({{ARRAYFORMULA(ROW(INDIRECT("A1:A"&COUNTA(
     QUERY(IMPORTRANGE("ID"; "MY!A2:A"); "select Col1 where Col1 is not null")))))\
     QUERY(IMPORTRANGE("ID"; "MY!A2:A"); "select Col1 where Col1 is not null")};
            {ARRAYFORMULA(ROW(INDIRECT("A1:A"&COUNTA(
     QUERY(IMPORTRANGE("ID"; "NY!A2:A"); "select Col1 where Col1 is not null")))))\
     QUERY(IMPORTRANGE("ID"; "NY!A2:A"); "select Col1 where Col1 is not null")}}; 
    "select Col2 order by Col1")

    0

  • also make sure that both spreadsheets have the same amount of rows

5
  • this formula outputs #REF! in two separate cells below each other withot any error message May 16, 2019 at 7:55
  • can you share a copy of your sheets?
    – user0
    May 16, 2019 at 8:27
  • Sheet A (source of data) link Sheet B (where the data should be imported and merged) LINK May 30, 2019 at 16:27
  • @MarekMarhoul answer and your sheet updated
    – user0
    Jun 14, 2019 at 7:20
  • sweet! Thank you, as always great work Jun 14, 2019 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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