Simply Retrieving Last Row Referenced in Another Sheet

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?

Tags:

Answers

  • Jeff Reisman
    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...

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!