How to create a dashboard that will change based on dates, and cause red flags for tasks

Phipps Lawrence

Hi Smartsheet group, I am trying to create a new dashboard that will show the below screenshot. This screenshot was taken from excel and is the layout we are hoping to be able to achieve on our dashboard.

The issue is each project has its own separate folder and the permits for each one will not be the same. Is there a way to create a formula that will allow for my tasks to flow and show what the next task will be and anticipated end date? The following screenshot is what a specific project plan looks like but our permits are not all the same for each project so they do vary.

Thank you for the help!

Hudson_Miller

@Phipps Lawrence

One approach would be to make a "Flag" or "Stage" or "Milestone" column on each of the sheets in question that has the same set of drop down values available, permit1 - permit(n). Then assign the rows in question with the respective value. Incidentally, you could use this column to also assign the other 'milestones' i.e. IC Status, Land Status, etc.

Put all these values onto a "sheet summary" for each project using either index match or direct cell references.

Then, build a "sheet summary report" for the sheets in question pulling the sheet summaries for each project together.

All the field names for the sheet summaries will need to be identical for the sheet summary report to work for you.

Good Luck!

Hudson

jmyzk_cloudsmart_jp

Hi @Phipps Lawrence

If you're looking to show the next task and upcoming tasks by assigned user and project, here's a method that worked well for us:

In our main project sheet, we added a helper column called [Latest Future Work for an Assigned To] with this formula:

=Start@row = MIN(COLLECT(Start:Start, [Assigned To]:[Assigned To], [Assigned To]@row, Start:Start, >=TODAY()))

https://app.smartsheet.com/b/publish?EQBCT=33e1824a88dc405384d08bace205aa6e

This identifies the next task (closest future start date) for each assigned user.

We also added columns to look up Folder and Permits using cross-sheet formulas like:

[Folder] = JOIN(COLLECT({Task ID to Folder & Items : Folder}, {Task ID to Folder & Items : Task ID}, [Task ID]@row))

[Permits] = JOIN(COLLECT({... : Permit1}, {... : Task ID}, [Task ID]@row)) + CHAR(10) + JOIN(COLLECT({... : Permit2}, {... : Task ID}, [Task ID]@row))

https://app.smartsheet.com/b/publish?EQBCT=6dffc845f0bf4998b0f8320c50cd05d4

Then we created two reports:

1. One filtered by Current User + [Latest Future Work for an Assigned To] = true – shows only the next task per user.
2. Another filtered by Current User only – shows all upcoming tasks for that user.

Both reports are shown on a dashboard with Viewer Mode set to "Their own perspective", so each user sees only their data, along with related Folder and Permits information pulled in from the lookup.

https://app.smartsheet.com/b/publish?EQBCT=cc5b24d5f0884ce598ce8b1702a74f9b (You will see nothing as you are not shared on the sheet and report and not assigned to any tasks.)

Jmiyazak's view

Demo's view

Hope this helps! Let me know if you'd like a formula breakdown or report settings example.