ImportRange for last non-empty column

Question

I'm trying to import current inventories from google-form based inventory sheets for multiple rooms (each room's inventory is on a different sheet). I'd like to import the last non-empty row/column from another sheet using ImportRange or similar to grab the last inventory that was done for that space. I've also tried importing these inventories into another sheet in the same document and use the "Sheet 1"!A:A format to grab the last occupied column, but I haven't found a way to grab all 42 cells in the column without needing to type them out one at a time using this formula

"=FILTER('Sheet 1'!2:2 , COLUMN('Sheet 1'!2:2) =MAX( FILTER( Column('Sheet 1'!2:2) , NOT(ISBLANK('Sheet 1'!2:2)))))"

But that just grabs a single cell , instead of the whole column. Any suggestions?

player0 · Accepted Answer · 2020-03-03 11:47:11Z

1

getting the last column:

=ARRAYFORMULA(INDIRECT(
 ADDRESS(2,         MAX(IF(2:2="",,COLUMN(2:2))))&":"&
 ADDRESS(ROWS(A:A), MAX(IF(2:2="",,COLUMN(2:2))))))

edited Mar 3, 2020 at 11:47

answered Mar 1, 2020 at 23:56

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Michael Hagen Over a year ago

Thanks! I have this working but I'm having trouble getting the data I want. I made another tab (Room 110) within the main spreadsheet (Main Inventory) where I imported the whole inventory spreadsheet (Room 110 Inventory) using "ImportRange" (with transpose since forms write in new rows instead of columns). The code you posted works great for grabbing the last column of this secondary sheet, but I can't access the cells from this second tab in the main one using 'Room 110'!B2. All the cells just show up blank. Is it possible to use your code to access the second spreadsheet using ImportRange?

Michael Hagen Over a year ago

Here is a link to the Main Inventory sheet and Here is a link to the sheet that is linked to and filled out by the google form I've disconnected the form because it allows any user to submit and I don't want to get flooded with submissions by bots or something because I posted the link.

player0 Over a year ago

try:

=TRANSPOSE(QUERY(IMPORTRANGE(  "1CLdHiJNNwJgYhJgkrbLiD6O76BuW_ROQTUL5fKsLoC4", "A2:AQ"),   "where Col1 is not null offset "&COUNTA(IFERROR(IMPORTRANGE(  "1CLdHiJNNwJgYhJgkrbLiD6O76BuW_ROQTUL5fKsLoC4", "A2:A")))-1))

player0 Over a year ago

docs.google.com/spreadsheets/d/…

Collectives™ on Stack Overflow

ImportRange for last non-empty column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related