Pull out some data from report into a metric sheet

edited 11/24/23 in Add Ons and Integrations


I have created a report that shows the manager's name with the count of missing ratings. I would like to pull out this information and put it into a metric sheet that would have the manager name in one column and against the name a column with the number of missing ratings.

I am very new to Smartsheet and despite browsing the community couldn't find an answer. What is the best formula for that? I have attached screenshots to better explain this.



  • David Ruff
    David Ruff ✭✭✭

    Hi schuefa,

    Welcome to the world of Smartsheet! If you have datamesh (a premium app offered by Smartsheet), you can create a workflow to copy/add data from your report to your metrics sheet.

    If you don't have datamesh, then there won't be a way to get data from a report to a sheet. However, you can look into cross-sheet references and incorporate them in formulas to extract the data you need. This link should help you get started: https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

  • Thank you @David Ruff We have datamesh indeed. I just tried it but from my understanding I would only be able to map the worker's manager into the new metric sheet is that correct?

  • David Ruff
    David Ruff ✭✭✭

    No problem, @schuefa - With datamesh, you choose the "unique identifier" that all the other data is tied to, such as a row ID, could be the manager column, could be something else. Not quite sure about your end goal based on the initial message. My preference when creating a metric sheet is to use cross-sheet references and formulas (COUNTIFS, SUMIFS, etc.)

    If I have to extract data from multiple sources (sheets), I use datamesh to compile all the data based on a unique ID, then use the cross-sheet formulas to build the metric sheet. I build the unique ID based on row ID (auto-number column) and add a prefix in the settings that would be unique to that sheet (e.g. sheet name = Engineering, prefix = "ENG-").

    I hope this helps, but if not, please provide images of the metric sheet or some more context and hopefully we can get this solved 😊.

  • Thanks @David Ruff for your help. I have not been able to make it work but found a workaround. Will deep dive on Datamesh functionalities later on. Thanks!