Copy the last populated row in a Google Sheet to a different spreadsheet

0
votes

I'm new to programming and would really appreciate some help.

I have two different spreadsheets, one named Database (this is where we put all the details) and the other is named Checklist, both have 1 worksheet each named Sheet1.

Now, what I wanted to do is to get the last populated row from the Database spreadsheet into the Checklist spreadsheet.

I know a formula like: 

=FILTER('Sheet 2'!A:A , ROW('Sheet 2'!A:A) =MAX( FILTER( ROW('Sheet 2'!A:A) , NOT(ISBLANK('Sheet 2'!A:A)))))

This gets the last populated row, but this only works within one spreadsheet and doesn't work when I'm working on two different spreadsheets.

How can I get this formula to work with 2 different spreadsheets?

Thank you so much

1
google-sheets
That formula only provides the last value in column A. Is that what you want, or do you want the entire row? - Diego
Hi @Diego I only want to get the last value, but I can't seem to do it using two different spreadsheets - L. Gold

1 Answers

1
votes

To access data from another spreadsheet, you can use IMPORTRANGE() (documentation). I suggest you create a new sheet in your Checklist spreadsheet and import the sheet you need from Database into it. Then you'll be able to use your formula.

  1. In Checklist, create a new sheet called "[IMPORTED] Database" (or something like that)
  2. In cell A1 of "[IMPORTED] Database", enter =IMPORTRANGE("database_spreadsheet_url", "Sheet1!A:Z") (modify the range to be what you actually need)
  3. Now adjust your formula for getting the last value to be =FILTER('[IMPORTED] Database'!A:A, ROW('[IMPORTED] Database'!A:A)=MAX(FILTER(ROW('[IMPORTED] Database'!A:A), NOT(ISBLANK('[IMPORTED] Database'!A:A)))))