Enhancement request for SIGHTS: Metric-/SheetData-Widget based on reports

Hi Dietrich,

Thanks for the good question.

The reason users can't select specific cells in a Report widget is that the report contents are dynamically generated based on a filter query, so we can't systematically know what the user would expect to happen if the report contents changed: Would they expect the links to be static pointers to the underlying sheet cells that they selected? Or would they expect the links to update along with the report if the data changed? If the latter, on what basis should the new data be selected? If rows are added or removed from the report, should that be reflected in the widget?

The answers to these questions could be different in each scenario. So for clarity, we separated them into different widgets: If users want a static link to a particular sheet cell, they can select it directly in a sheet using the Metric or Sheet Data widget. If they want the widget content dynamically generated by a query, they can select an entire report using the Report widget.

To group data, we've found it works well to create multiple reports, each filtered using the criteria by which you want to group. (Note: We are working on reporting enhancements that will enable users to group and summarize within a single report.)

All that said, we'd love hear more details about exactly what you are trying to do in your scenario. You'd be welcome to share a screenshot of your Sight if you feel comfortable with that.

Thanks,

Daniel

Thanks Dietrich for the additional details.

VLOOKUP is something we're looking at adding in a future release.

In the meantime, I agree it seems like having a static row at the top of your sheet would work as the source of a Metric widget. Rather than copying the data to the top row, can you reference the data below using simple formulas, e.g., =[Status Quality]4, =[Status Budget]4, etc. ? Then to maintain the Sight, you would increment the row reference number each month, e.g., =[Status Quality]5, =[Status Budget]5, etc. . Let me know what you think.

Best,

Daniel

Hi Dietrich,

That's an interesting idea. While Smartsheet doesn't currently support the OFFSET formula (which would offset a cell reference by a certain amount), you could accomplish the auto-updating summary cell for your users via a branching IF formula as follows:

1. Enter =TODAY() in Primary1 to dynamically show today's date.

2. Enter =MONTH(Primary1) in Primary2 to show the current month (numerical).

3. Now, create a branching IF statement to reference the right value depending on the month. It will look something like this (for as many branches as you like):

=IF(Primary2 = 7, Primary4, IF(Primary2 = 8 , Primary5, IF(Primary2 = 9, Primary6)))

This formula will update every time someone opens the sheet. (Note that the =TODAY() function won't update if nobody opens the sheet.)

Hope this helps!

Best,

Daniel

Great, thanks Dietrich for the update! Let us know if we can help further. Meanwhile, we've noted your scenario as a good one to keep in mind when we implement VLOOKUP.

Best regards,

Daniel