Best Of

Hey Smartsheet Community! I am posting this in response to the Engage Product Vision sessions that Ben Canning hosted, where there was some talk about someday exporting dashboard content other mediums such as PDF, Powerpoint, Google Slides, etc. In my company's change management journey we have been working hard to standardize a per-project dashboard layout that can easily be snipped and pasted into those other layouts. Reality is executives want the 'highlights': talking points they can read off-line, share, print, or copy/paste, all without having to sit through a project update meeting.

If your PPM weekly rollup is going to be consumed off-line by leaders, one key requirement is to include story bullets, not just graphical indicators. There lies my challenge: How to build a dashboard layout that honors the indent or 'nesting' of text bullets?

In Powerpoint, Slides, Word, or Email, we often communicate with nested bullets or numbers, like this:

In Smartsheet, you can do this within a sheet if the update is your primary column. Many of us probably have variations of this:

However, we break our updates into 3 key areas: Current Status, Recent Accomplishments, and Upcoming Activities, like this:

Unfortunately, only the primary column can indent, so we needed another way to visualize each of the 3 buckets. Likewise, the primary column indenting can only be viewed in a sheet, not in a report or dashboard. I have tried multiple formulas to insert spaces or blank ascii characters to force the indenting, with no success. There are a handful of community articles about leading whitespaces with formulas, and they all seem to land on the same problem, here's an example:

https://community.smartsheet.com/discussion/26966/cell-with-leading-whitespace

The workaround we have landed on is this:

1. PMs enter their updates in a single row per update (no indented child rows)

2. PMs are trained to use alt-enter within the cell to separate their thoughts and keep it short, like you would in a powerpoint (no run-on sentences, no extra words)

3. We have a hidden locked row in the Summary section that uses index to collect the row with the most recent update, and then convert their content into bulleted format in 3 fields using ALT characters (e.g. https://tools.oratory.com/altcodes.html)

4. We deliver those 3 fields to the project dashboard using Metric Widgets

Our first row of data sits in row 24. There is a helper column called "Dates with an Update" that makes sure the row we grab actually has content. The formulas are:

="• " + SUBSTITUTE(INDEX(Accomplishments24:Accomplishments124, MATCH($[Last Update]@row, $[Dates with an Update]24:$[Dates with an Update]124)), CHAR(10), CHAR(10) + " • ")

="• " + SUBSTITUTE(INDEX(Accomplishments24:Accomplishments124, MATCH($[Last Update]@row, $[Dates with an Update]24:$[Dates with an Update]124)), CHAR(10), CHAR(10) + " • ")

="• " + SUBSTITUTE(INDEX([Upcoming Activities]24:[Upcoming Activities]124, MATCH($[Last Update]@row, $[Dates with an Update]24:$[Dates with an Update]124)), CHAR(10), CHAR(10) + " • ")

Each PM then snips this portion of their dashboard and pastes it into Powerpoint. We collate and send out to our executive team weekly.

The drawback of this approach is we still lose the nesting. We have another version in testing that uses multiple rows to allow primary column indenting, and then applies an alternate character from the ALT-map. This is actually more complex and fragile because users visually lose track of whether their indented row is level 2,3, 4, etc. We're trying to solve for that with more training and more conditional formatting, but it's just not ideal.

Summary: When Smartsheet works on the design for extracting dashboard content to other mediums, I hope they will first deliver a method to show indented content in reports and dashboards. Bonus points if we could indent in columns other than the primary column, but I suspect that's far fetched.

Challenge: Any community users out there have a better way to nest and indent text content within a dashboard widget?