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
  • 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

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

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.