indexing - How do I import data from the last occupied cell in a column from a separate spreadsheet?

Question

So I've been running into an issue combining the two functions listed in the title. I'm trying to import the last occupied row in column E in a separate spreadsheet. Here is my current code: IMPORTRANGE("other_spreadsheet","specified tab in spreadsheet",INDEX(E2:E,COUNTA(E2:E)))

Any assistance would be appreciated.

question from:https://stackoverflow.com/questions/66048330/how-do-i-import-data-from-the-last-occupied-cell-in-a-column-from-a-separate-spr

Answer 1

try:

=QUERY(IMPORTRANGE("URL_or_ID", "Sheet1!E2:E"), "where Col1 is not null", 0)

or:

=IF(A2=1, IMPORTRANGE("URL", "Sheet1!E2:E"&
 MAX(IFERROR((IMPORTRANGE("URL", "Sheet1!E2:E")<>"")*(ROW(2:100000))))), )

---

UPDATE 1:

delete column B and use in B2:

=ARRAYFORMULA(
 IF(A2:A=1,IMPORTRANGE("1kkMHXRaaPv0IMZgFofXPROeCNL8OLvyPxoT60gKuryg", "Purchase_Order!E2:E"),
 IF(A2:A=2,IMPORTRANGE("1kkMHXRaaPv0IMZgFofXPROeCNL8OLvyPxoT60gKuryg", "High_Tower!C2:C"),
 IF(A2:A=3,IMPORTRANGE("1kkMHXRaaPv0IMZgFofXPROeCNL8OLvyPxoT60gKuryg", "Hotline!D2:D"), ))))

---

UPDATE 2:

delete column B and use in B2:

=ARRAYFORMULA(
 IF(A2:A=1,IMPORTRANGE("1kkMHXRaaPv0IMZgFofXPROeCNL8OLvyPxoT60gKuryg", "Purchase_Order!E"&MAX(
  IFERROR((IMPORTRANGE("1kkMHXRaaPv0IMZgFofXPROeCNL8OLvyPxoT60gKuryg", "Purchase_Order!E2:E")<>"")*(ROW(2:10000))))),
 IF(A2:A=2,IMPORTRANGE("1kkMHXRaaPv0IMZgFofXPROeCNL8OLvyPxoT60gKuryg", "High_Tower!C"&MAX(
  IFERROR((IMPORTRANGE("1kkMHXRaaPv0IMZgFofXPROeCNL8OLvyPxoT60gKuryg", "High_Tower!C2:C")<>"")    *(ROW(2:10000))))),
 IF(A2:A=3,IMPORTRANGE("1kkMHXRaaPv0IMZgFofXPROeCNL8OLvyPxoT60gKuryg", "Hotline!D"&MAX(
  IFERROR((IMPORTRANGE("1kkMHXRaaPv0IMZgFofXPROeCNL8OLvyPxoT60gKuryg", "Hotline!D2:D")<>"")       *(ROW(2:10000))))), ))))

demo spreadsheet

---

UPDATE 3:

use in B2 and drag down:

=IF(A2=1, INDEX(IMPORTRANGE("1kkMHXRaaPv0IMZgFofXPROeCNL8OLvyPxoT60gKuryg", "Purchase_Order!E2:E"), COUNTIF(A$2:A2, 1)),
 IF(A2=2, INDEX(IMPORTRANGE("1kkMHXRaaPv0IMZgFofXPROeCNL8OLvyPxoT60gKuryg", "High_Tower!C2:C"),     COUNTIF(A$2:A2, 2)),
 IF(A2=3, INDEX(IMPORTRANGE("1kkMHXRaaPv0IMZgFofXPROeCNL8OLvyPxoT60gKuryg", "Hotline!D2:D"),        COUNTIF(A$2:A2, 3)), )))

demo spreadsheet