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

Dietrich Koch

I'm missing the ability to insert data into Sights based on specific fields or ranges of a report. Currently one can "only" insert a report as a whole.

 

If you want to show certain fields or ranges like with the metric widget or sheet data widget it is only possible to pick them from sheets, not from reports. Why is that?

 

With reports one can easily preselect information and it would be great if these informations could be incorporated into Sights. Additionally one could slice the information and spread it over several widgets to group the data according to individual layout criteria...

 

Best 

Dietrich

Daniel Stein

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

Dietrich Koch

Hi Daniel,

 

sorry for my late reply…

 

Here is what I’m trying to do: It’s about setting up a monthly reporting in Smartsheet.

 

I have a sheet to collect details for the monthly report (see picture with dummy)

 

Now the problem is, that all the widgets can not access reports. The only widget that can do is the report widget, but it does not show cell values like the others do.

 

Perfect would be to have a function like VLOOKUP. It could be used in the sheet to search for the row with the checkbox and then take all the values into the row at the top of the sheet, but unfortunately this function is not available within Smartsheet.

 

 

Do you have an idea of this could be solved?
An approach?

 

Best

Dietrich

Daniel Stein

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

 

 

Dietrich Koch

Hi Daniel,

 

I'm trying to reduce the manual efforts for colleagues working with the sheets in order to reduce errors, so I would love to find a workaround for my problem without having them to change every formula in the cells...

 

Another workaround could be to have a cell where the colleagues enter the rownumber of the actual month. To formula in top row cell [Primary6] references to this cell in its formula and gets the value of the cell of the actual month.

 

In the example below the row number of the actual month is entered in cell Primary3. The formula in the "top row" cell in Primary6 takes the number 9 to get the content of the row Primary9. Result should be, that the cell in Primary6 shows "June 2016".

 

 

What am I doing wrong?

Can you help me with this formula?

 

Best 

Dietrich

Daniel Stein

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

Dietrich Koch

Hi Daniel,

 

thanks for your reply - it gave me the input I needed to solve my problem!

 

Indeed I forgot, that Smartsheet does not have the limitation of 7 nested IF-functions like e.g. Excel has...

 

Now I have an additional column with a dropdown where the project manager can select the month/year he wants to report and all the cells in the top row now take a look at this cell and take the value of the column of the corresponding row if it matches. In the end there are 37 IFs in each top row cell but it works perfectly!!

 

Thanks again!

Daniel Stein

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

mattc

I also want to be able to paste in a range of cells.  I can choose a single value or a picture, so why not make it possible to insert the content from a specific sheet cells A1 to B3 and let me choose whether to keep or strip the formatting?