Simply Retrieving Last Row Referenced in Another Sheet

kmforecasting

Simply, I need to reference another sheet and get all column values on the LAST row!!!

I thought this should be simple to do by indexing another sheet, then capturing the last row and include all column values in this last row???

See Example:

=INDEX({EXTERNAL SMARTSHEET Range 20}, LAST ROW, ALL COLUMNS)

What am I doing wrong?

Jeff Reisman

So you want the values from the entire last row of another sheet to be shown in a single cell on another sheet? Or you want to pull all those column values into columns on your other sheet? Either way that's not going to work the way you're trying to do it.

For one thing, how is Smartsheet supposed to know what the LAST row is? Any time a row is added or removed, or the sheet is resorted, the last row will no longer be the last row.

Here's one way to do it:

On your first sheet, create an auto-number column.

On your second sheet, create a field called "LastRow" to collect the highest value in the auto-number column:

=MAX({AutoNumber Column on First Sheet})

use a series of INDEX/MATCH formulas to gather your last row values:

=INDEX({First Column You Want}, MATCH(LastRow@row, {AutoNumber Column on First Sheet}, 0))

You can do this in each individual column you want, or if you want all the values in the same cell, place all your INDEX/MATCH formulas inside a JOIN:

=JOIN(INDEX({First Column You Want}, MATCH(LastRow@row, {AutoNumber Column on First Sheet}, 0)) + " " + INDEX({Second Column You Want}, MATCH(LastRow@row, {AutoNumber Column on First Sheet}, 0)) + " " + (INDEX...