Appending / Adding manual data input to end of IMPORTRANGE dataset

Question

I am trying to figure out how to append / enter manual data to the end of an IMPORTRANGE list. In my spreadsheet, each tab is a month. I want to IMPORT the prior month's list (rows) and then be able to manually add the new month's entries (rows) to the end of the imported data set.

Is there a way to command each subsequent month's tab to import the prior month's list and allow new current month entries to be made - and so on?

For example: Tab January - there are 5 names (and related information), I would like to pull those 5 names into Tab February and then be able to add February's new names (and related information) to the end of the imported January list.

Currently with the formula IMPORTRANGE("https://docs.google.com/spreadsheets/d/1vTcYJ8P5Ai3g4EME8Ye6B3qT0KHKNlr5pP7jpTPIxl4/edit","Attendance Jan!A3:E50") when I go to manually enter at the end of the imported list, I am understandably getting the error "Array result was not expanded because it would overwrite data in A8."

Conversely, maybe there is an option to tell the import to pull the prior month's fields until a blank (end of dataset) occurs?

Answer 1

If you only need 5 rows then use: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1vTcYJ8P5Ai3g4EME8Ye6B3qT0KHKNlr5pP7jpTPIxl4/edit","Attendance Jan!A3:E7")

---

The formula is importing 48 rows you will be able to write on the row 49 after the row where the IMPORTRANGE formulas was added. Let say that the formula was added row 10 you will be able to add values on row 59.

If you don't need so many rows then change the second parameter of IMPORTRANGE.

Answer 2

This formula gets just the contacts where Month Added matches the month in cell A2:

=filter('Contact List'!A4:F, 'Contact List'!A4:A = A2)

See the new 'Attendance Jan - filter()' sheet in your sample spreadsheet. The formula is in cell A3.

Note: it appears that your intention is to manually add data side-by-side with the formula that reads data from the 'Contact List' sheet. This exposes you to the risk of rows getting misaligned. This will not be an issue if you never insert or delete rows in the middle of 'Contacts List' and always append new rows at the end of the list, but you should be aware of the issue. Lance has given a thorough treatment of the row misalignment issue and how it can be dealt with in some cases.

For posterity, please find additional notes below.

Use this to import the data from another spreadsheet file:

=query( importrange("1vTcYJ8P5Ai3g4EME8Ye6B3qT0KHKNlr5pP7jpTPIxl4", "Attendance Jan!A3:E50"), "where Col1 is not null", 0 )

When the data is in the same spreadsheet file, you do not need to use importrange(). There are many ways to copy data from one sheet to another, such as an { array expression }, like this:

={ 'Attendance Jan'!A3:E50 }

...or a normal range reference in a query(), like this:

=query('Attendance Jan'!A3:E50, "where A is not null", 0)