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?
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 137 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!