Best Of

@Meredith Rhodes This is actually a bit different from yours.

We do pull the RIGHT 4 for the year, but then we use a LEFT function to pull the left characters with the FIND telling us where to STOP for the month number.

Your formula has a few misplaced parenthesis, but even then we would have a few issues because it is looking for a day value.

What we did above in the working one is essentially

=IFERROR(DATE(yyyy, mm + 1, 1), DATE(yyyy + 1, 1, 1)) - 1

We use a DATE function and add 1 to the month number. Of course this will error out for December dates because there is no month 13. So we use an IFERROR to bump it to January of the following year. Both DATE functions will output the first of the next month so that when we subtract 1, we automatically have the 28th, 29th, 30th, or 31st as needed.

To add a year to the output, we would add one year to both DATE functions.

=IFERROR(DATE(yyyy + 1, mm + 1, 1), DATE(yyyy + 2, 1, 1)) - 1

@Dhoke you can use that automation for the column you want to run it in. Just build a formula for the date in that column. For instance, if you have an Initial Date column and a Run Date column being the execution. You can put the following formula in the Run Date

=DATE(YEAR(TODAY()), MONTH([Initial Date]@row), DAY([Initial Date]@row))

@SmartsheetRookie

Sure. You can create an automation for this.

Trigger: When a row is changed, when Completed changes to Checked

Action: Move a row (select the sheet to move it to.)

Ahh, very interesting. Thanks for clarifying this. In this case, I do believe the students would need to make a Smartsheet account, or else Smartsheet would not know how to match them to the Contact column in order to display the rows of the current user.

To your second question: When you share a Dashboard with someone, they can only see the data presented in a widget if they have access to an underlying report. Otherwise, they would see this message for any widget to which they don't have at least viewer access to the source sheet:

One way around this may be to create a Dashboard with a filter (unfortunately this is where my expertise ends - I only recently learned about this at ENGAGE 2023 and have not yet had a chance to dig into how it's done and implement it in my dashboards). But, from what I saw, I do believe this could be a viable solution for what you need to do. If I recall correctly from the demo session I attended at ENGAGE, the filtered dashboard uses a form to select the filter. So, you'd have a form of all your students names, and the data displayed on the dashboard would change based on which student is selected (so, it would sort of work like your "current user" view, except the students wouldn't have to have a Smartsheet account to view their data). I know the demo also included a title field so you could see what filter was applied to the dashboard, so that would solve your need for wanting the name of the student at the top of their view.

If this works the way I think it would, I believe you could still publish the Dashboard in "View Only" mode, thus saving your students from having to make a Smartsheet account, and not have to share them to all the underlying sheets. The potential downside would be if there is sensitive student information on the dashboard, because there would really be nothing stopping Student #1 from selecting the filter to view Student #2's data.

I'm sorry I can't walk you through the details of filtering on dashboards, but there should be a recording from ENGAGE or you may be able to find someone else on here with that expertise.