1

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?

1
  • Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. Jun 2, 2021 at 8:09

2 Answers 2

0

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.

5
  • Ruben, Thanks for the suggestion. Unfortunately, the number of rows being imported is unknown each month...in my specific example, it was 5 but it can be any number. Is there a way to just command to pull all rows up to where column A (aka column 1 of data) becomes blank/null?
    – tami
    Jun 2, 2021 at 5:07
  • You might use IMPORTRANGE inside QUERY or FILTER to get rid of certain rows. i.e. Using FILTER or IMPORTRANGE to bring in and filter data from another spreadsheet, Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col7
    – Rubén
    Jun 2, 2021 at 5:15
  • Ruben thanks soooo much for the suggestion. I am going to review the suggestion to posted. I am green to the filter/import combo :) Can you let me know if you suggestion is similar to doubleunary's guidance in this thread? I truly appreciate everyone's help.
    – tami
    Jun 9, 2021 at 19:25
  • FILTER and QUERY could be used together with IMPORTRANGE to filter data so yes, my suggestion is similar to the one posted by doublenuary
    – Rubén
    Jun 9, 2021 at 19:49
  • Thanks Ruben!!!!
    – tami
    Jun 10, 2021 at 0:25
0

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)

7
  • Great question. I need to prepend the data as we need to capture and quantify/measure new attendees each month and to log periodic attendance for each attendee each month. The thought was to log month 1 attendees and attendance for the month in month 1 tab - then append month 1 attendees into month 2, adding new month 2 attendees as they occur, and log attendance for this new (appended plus new manually entered) month 2 attendees. Hope that makes sense :)
    – tami
    Jun 2, 2021 at 10:31
  • Is this an XY problem? It is quite straightforward to write filter() and query() formulas to aggregate data that spans multiple months, picking only the month(s) that you are interested in. Copying data should not be required — just put all data in the same sheet. Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. Jun 2, 2021 at 17:46
  • Doubleunary, awesome suggestion & it seems like a great idea. I am not familiar with how to execute that combo formula. Here is the editable link. - my objective is to have the "Contact List" tab be the name/info entry (columns A-F) log which then each month (defined by user in column "A" - "Month Added" of "Contact List") is automatically carried forward into the respective attendance tab ("Attendance Jan", "Attendance Feb", so on). To further clarify, each month attendance tab would only have that month carried forward. In the link, I manually copy & pasted these in and highlighted in yellow
    – tami
    Jun 9, 2021 at 19:16
  • editable link: docs.google.com/spreadsheets/d/…
    – tami
    Jun 9, 2021 at 19:20
  • My current battle to note is with the import range in each Attendance tab, I result a #REF! error as the import cannot write in subsequent tabs due to my formulas at the bottom of the tab which I need.
    – tami
    Jun 9, 2021 at 19:23

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.