Pull out some data from report into a metric sheet

schuefa

Hello,

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.

Smartsheet report question.docx

Uploaded Nov 21, 202360.04 KB

Thanks!

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

DataMesh

https://www.smartsheet.com/marketplace/premium-apps/datamesh

Eliminate typos, duplicative data entry, and unnecessary work by mapping data between large sheets and reports.

schuefa

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

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 😊.

schuefa

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!