Looking for a way to show last 5 entries in a sheet

SteveW

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

SteveW

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

Jason Tarpinian

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.

Michelle Choate 2

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.

SteveW

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

SteveW

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