Report with the last entry to each person's forms

Carla_Ahzul

Hi!  I  need  to know  how  I  can  make a  report  with  last  entry  to  each  "assigned  to"  with  the row that  generated into the Form. Anybody know  how  do it?

Schiff A.

Hello,

To create the report that you're looking for you'll need to set up a check box with a formula that looks at each possible assignee and then selects the most recent entry for that assignee.

You can accomplish this by doing the following:

1. Create a "Created (Date)" column on your sheet: https://help.smartsheet.com/articles/1964567-system-columns

2. Create a checkbox column called "Most Recent" and enter this formula in each row: =IF([Created]@row= MAX(COLLECT(Created:Created, [Assigned To]:[Assigned To], =[Assigned To]@row)), 1, 0)

This will check the box for the latest entry for each assignee.

3. Create a report that includes items where the "Most Recent" checkbox is checked.

 

Thanks!

Schiff

Smartsheet Support

T.J. Williams

Does this formula work in the US as well? I keep getting #UNPARSEABLE

Using:

=IF([smartsheet row added date]@1 = MAX(COLLECT(smartsheet row added date:smartsheet row added date, [Sales Rep]:[Sales Rep], =[Sales Rep]@1)), 1, 0)

TJ

Genevieve P.

Hi @T.J. Williams

It looks like there's a typo after the first @row, try this:

=IF([smartsheet row added date]@row = MAX(COLLECT(smartsheet row added date:smartsheet row added date, [Sales Rep]:[Sales Rep], =[Sales Rep]@row)), 1, 0)

You can read about the @row function in our Help Center (click here).

Cheers!

Genevieve

Amy Arnold

This thread helped me pull the most recent submissions from each location into a report using the formula below so thank you.

=IF(Created@row = MAX(COLLECT(Created:Created, Center:Center, =Center@row)), 1, 0)

I have a couple of questions:

Is there a way to check the box for the "Most Recent" entry that is not for the current day?

For example, today is 06/03/21 and I want to check the entries from the previous day which would be 06/02/21. So the formula would ignore entries for the current day.

I would like to apply conditional formatting for entries that have:

◾ Most Recent: Checked

◾ Created: Is older than two days

Very appreciated!🙌

Genevieve P.

Hi @Amy Arnold

We can actually build that criteria right into your COLLECT formula! We can look for the MAX date that is not Today.

Try this:

=IF(Created@row = MAX(COLLECT(Created:Created, Center:Center, Center@row, Created:Created, <> TODAY())), 1, 0)

It's just another Range and Criteria to add into the Collect function. Then your checkbox will only check for the most recent date that isn't today. Will this work for you?

Cheers,

Genevieve

Amy Arnold

@Genevieve P the formula you shared works perfectly! I really appreciate your help. 🙌

Genevieve P.

No problem, @Amy Arnold! 🙂

Sarah_H

Hi, @Genevieve P. It looks like you helped other people with kind of formula, so I'm hoping that you can help me out, too! I'm managing a data entry project and trying to be as efficient as possible.

I have a smartsheet with a list of all the links to all the job postings that need to be entered. I plan to have my team self-assign themselves to one job posting at a time. When they are done entering all the applicants from one job posting, they will go back to the main sheet and assign themselves to the next job posting.

I'm setting up a dashboard for each team member so they can have everything they need at their fingertips. I want a way to have the link to the current job posting they are working on displayed on their dashboard.

I was thinking that I need some kind of formula that takes the Max date modified for each specific team member, and then Matches it the correct link to the job posting. Then I can have this as Metric on the dashboard and they can always have it automatically updated based on the job posting they are currently working on.

Here are the columns I'm working with:

Assigned_To, Modified, Link

Thank you!

Genevieve P.

Hi @Sarah_H

What about creating a Report instead?

You could filter the Report so it doesn't show anything that's been "Completed" (assuming you have a Status column that they're completing) or "Not Started", etc.

Then you could also filter the Report by "Current User" so it only displays the row associated with the user who's looking at the Report. (Or could you Group it by the Assigned To column so they can easily find their own row and see what everyone else is working on, too.)

Finally, you can use a Report Widget to put this on your Dashboard and it will automatically update as they update the sheet.

This may be faster to set up than a formula. Thoughts?

Sarah_H

@Genevieve P. Thanks so much for your suggestion! I think you're right that there has to be an easier way than setting up such a complex formula. Every time I tried to set up a formula, there was an issue. I think there may be a bug with the Max Date Modified formula. It wasn't showing up the correct information. Additionally, I discovered that if I would use a metric, the link would show up but would not be clickable. So that would be no use for me.

Your idea is a great solution and I already set it up. The only issue here is that the report widget has the option to have someone view the report from their own perspective. However this option doesn't work with a published dashboard, even if the user is logged into smartsheet.

I'm tempted to set up a seperate report for everyone... but I think it will get too messy for me.

Alas, we won't be able to use published dashboards, even though I love how clean and undistracting they are. If you have a better solution, please let me know.

Thank you!

Genevieve P.

Hi @Sarah_H

You're right, users would need to be logged in to Smartsheet and looking at a shared Dashboard in order for the report to filter to show only their own rows. You could set up one Report grouping by each person's name... this would be similar to having an individual Metric Widget for each user. This way you could display it in a Published Dashboard but it wouldn't be dynamic per-person.

scromer

Hello, I went ahead and used your formula above and works like a charm, but i have a problem. The new records that the users are entering by using a form are no longer appearing on the sheet.

I freaked out and deleted the formula and when i refreshed the records appeared with time stamp of when i deleted the formula.

How can i fix this error?

This is my formula:

=IF(Created@row = MAX(COLLECT(Created:Created, [Created By]:[Created By], =[Created By]@row)), 1, 0)

Genevieve P.

Hi @scromer

How are you applying the formula to the sheet, is this a Column Formula or did you drag it down into empty rows?

If you drag it down into "blank" rows, this means those rows are no longer blank so new form submissions can't go into those rows. New entries will appear underneath the last row with a formula.

Instead, try applying it as a Column Formula. This will keep blank rows as "blank" so new form submissions will appear immediately below your last row. See: Set Formulas for All Rows with Column Formulas