CODEWITHSUNDEEP
google-sheets
auxilium
auxilium

Reputation: 9

Google Sheets - get the right-most / last non-empty value of a row from imported sheet

I have a sheet which imports data from a different sheet. I inserted the sheet ID of the imported sheet in cell A1 to make it easier for me.

I need something that I can give a ROW and it will always return the right most / last non-empty value. That is, it automatically updates whenever we add a column in the imported sheet.

I've found this formula to show the last value of row 9 of the same sheet:

=FILTER(9:9,COLUMN(9:9)=MAX(FILTER(COLUMN(9:9),LEN(9:9))))

But I'm struggling to show the last value of row 9 of the IMPORTED sheet. How can I connect this formula with the imported sheet, that means with the Sheet ID (inserted in my new sheet in cell A1)?

Thanks a lot!

Upvotes: 0

Views: 2614

Answers (1)

pnuts
pnuts

Reputation: 59485

Assumes the relevant row number is entered in A2. Ugly but might be good enough:

=index(indirect("Sheet!"&row(A2)&":"&row(A2)),MATCH(1E+100,indirect("Sheet!"&row(A2)&":"&row(A2),1)))

Replace Sheet as appropriate.

Upvotes: 1

Related Questions