Looking for a way to show last 5 entries in a sheet
All,
I am new to Smartsheets and am looking for a solution to show the trailing entries on our dashboard from an excel sheet. The Report option allows for selecting how many rows to show but starts from the top row. Below is what I have tried so far.
First I created a sheet from the excel sheet
Then I made a report of the sheet
I then made a Report widget on my dashboard to show 2 rows of my report. The whole purpose of this effort is to show the trailing few entries of the excel sheet on a dashboard. The list is updated often so it needs to be updated on our dashboard periodically.
There may be a much easier way to do this, but I'm not sure how.
Appreciate any suggestions or guidance. I have not done any programming in Smartsheets so please be specific when suggesting code solutions.
Thank you,
Steve
Best Answer
-
I don't know why but today when I tried the exact same thing both formula's worked. Good job! Thank you so much!
Steve
Answers
-
You can do this with a couple helper columns. First build the Row # column. Create a new column "Row ID" and select it as a Auto number column. Then in another "Row #" column, use the formula:
=MATCH([Row ID]@row, [Row ID]:[Row ID], 0)
This will give you a Row # to reference. The build a checkbox column, call it whatever you want (this will be used to filter your report) and use this formula:
=IF([Row #]@row >= LARGE([Row #]:[Row #], 5), true, false)
That will then check the last 5 rows of your sheet with a checkbox, which can then be used as a filter in your report. LARGE() works by finding the n'th largest value in a range, so once we find that we just compare it to the row number at hand and if it's equal to or larger, it checks off the box. This formula will be dynamic so as you add new rows to the bottom of the sheet, it will grab the latest 5.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
Ok - I have a solution for you, but you are going to need to add 3 Columns. The first is going to be an AUTO NUMBER row called "Auto Number". The second column is going to be "Row #", and the third is called "Rank".
Formula for 'Row #' is the following = MATCH([Auto Number]@row, [Auto Number]:[Auto Number], 0)
Formula for 'Rank' is the following = RANKAVG([Row #]@row, [Row #]:[Row #], 0)
Now set your report with a filter based on the 'Rank' column and use only numbers 1-5.
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with!
-
Hi Jason and Michele,
Below is the error I receive when trying to build the autonumber column. I'll have to fix that before I'll know if the third step in each of your solutions work. It says unparseable… did I create the column in wrong format?? Michele I duplicated this with your solution and run into the same issue.
Thank you,
Steve
-
I don't know why but today when I tried the exact same thing both formula's worked. Good job! Thank you so much!
Steve
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives