Metrics Sheet Pulling from Reports

Techspan CT
Techspan CT ✭✭✭
edited 06/21/21 inFormulas and Functions

Hello All,

I'd love to know if its possible (or a workaround) for creating a metric sheet to use in a dashboard using data from reports.

We have 90+ Sheets that we can easily pull data into a report but its hard to get that into a meaningful chart. One specific example is we have 90+ sheets with rows that contain a rating from 1 to 10 and the date it was entered. We can pull it into a report showing the name, rating and date but cant seem to find a way to show this on a chart with an average rating across all entries by date.

What I would love to do is create an index/collect/match formula that can return an average of scores based on the date pulling from the report. I know I can build this out with individual references to all 90+ sheets but it is very tedious and not very flexible!

Any Ideas?

Tags:
· Share on Facebook Share on Twitter

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    At this time there is no way to use a formula to reference a report. I would suggest submitting a product enhancement request when you are able as that would be a pretty useful feature.


    In the meantime, there are a couple of different ways to consolidate this data into a more useful format. It sounds like all of the sheets contain the same data that you are wanting to pull and they are all in the same layout (similar to using a template). If that is the case then the premium app Control Center would be an option. If that won't work for you, then you can use cell linking to link each sheet to a master sheet and then run your calculations based on this consolidated data. It would be a bit of work pulling in cell links from 90 existing sheet, but it should only take an extra minute or so as new projects are added to link each new sheet to the master sheet as they are created.

    thinkspi.com

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
@Ymandel<\/a> See if this version works:<\/p>

=COUNTIFS([Discharged Date]:[Discharged Date], >DATE(2023, 5, 26), [Date Activated]:[Date Activated], <=(DATE(2023, 6, 2)), [Status]:[Status], OR(@cell = \"Active\", @cell = \"Discharged\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107013,"type":"question","name":"#Incorrect Argument Set","excerpt":"Good Morning, I've been trying to figure this formula out but I can't seem to get it. I need to sum any amount in the computed count column, match it with the AP tech for a specific month. I've tried swapping the columns around but nothing seems to work. What am I missing? =SUMIFS({011-AP Archive AP Tech}, [Primary…","snippet":"Good Morning, I've been trying to figure this formula out but I can't seem to get it. I need to sum any amount in the computed count column, match it with the AP tech for a…","categoryID":322,"dateInserted":"2023-06-28T13:44:50+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T14:41:30+00:00","insertUserID":156010,"insertUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-28T14:41:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":156010,"lastUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-28T14:41:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":11,"score":null,"hot":3375923780,"url":"https:\/\/community.smartsheet.com\/discussion\/107013\/incorrect-argument-set","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107013\/incorrect-argument-set","format":"Rich","lastPost":{"discussionID":107013,"commentID":382850,"name":"Re: #Incorrect Argument Set","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382850#Comment_382850","dateInserted":"2023-06-28T14:41:30+00:00","insertUserID":156010,"insertUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-28T14:41:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T14:41:39+00:00","dateAnswered":"2023-06-28T14:41:30+00:00","acceptedAnswers":[{"commentID":382850,"body":"

with a little more work - I got it!!<\/p>

=SUMIFS({011-AP Archive Computed Count}, {011-AP Archive AP Tech}, [Primary Column]118, {011-AP Archive Month}, 3)<\/p>

I swear I tried this earlier!! But I did confirm that it is adding up correctly!!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107009,"type":"question","name":"I need help with a if formula to get the $ amount total if another column = a specific item.","excerpt":"I want to make a formula in the Column \"A Total\" in the highlighted Cell, that will give me the Sum of $ from Grand Total Column if the A is in the Project column.","snippet":"I want to make a formula in the Column \"A Total\" in the highlighted Cell, that will give me the Sum of $ from Grand Total Column if the A is in the Project column.","categoryID":322,"dateInserted":"2023-06-28T12:55:48+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T13:46:52+00:00","insertUserID":162272,"insertUser":{"userID":162272,"name":"Elayne Smith","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Elayne%20Smith","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!z_neMx7Hvj4!mVDfnuqtMWQ!RH9Ly5uQ-po","dateLastActive":"2023-06-28T13:41:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162272,"lastUser":{"userID":162272,"name":"Elayne Smith","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Elayne%20Smith","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!z_neMx7Hvj4!mVDfnuqtMWQ!RH9Ly5uQ-po","dateLastActive":"2023-06-28T13:41:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":27,"score":null,"hot":3375918760,"url":"https:\/\/community.smartsheet.com\/discussion\/107009\/i-need-help-with-a-if-formula-to-get-the-amount-total-if-another-column-a-specific-item","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107009\/i-need-help-with-a-if-formula-to-get-the-amount-total-if-another-column-a-specific-item","format":"Rich","lastPost":{"discussionID":107009,"commentID":382831,"name":"Re: I need help with a if formula to get the $ amount total if another column = a specific item.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382831#Comment_382831","dateInserted":"2023-06-28T13:46:52+00:00","insertUserID":162272,"insertUser":{"userID":162272,"name":"Elayne Smith","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Elayne%20Smith","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!z_neMx7Hvj4!mVDfnuqtMWQ!RH9Ly5uQ-po","dateLastActive":"2023-06-28T13:41:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/N80WFJ95P6OH\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T13:45:50+00:00","dateAnswered":"2023-06-28T13:05:16+00:00","acceptedAnswers":[{"commentID":382818,"body":"

Hi @Elayne Smith<\/a>,<\/p>

The formula for this would be:<\/p>

=SUMIF(Project:Project, \"A\", [Grand Total]:[Grand Total])<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps - any issues etc. then just post! 😊<\/span><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Coiuntifs Formula Help Needed

    Hi! Can anyone please assist with the correct Formula? i'm trying to count how many rows are on a sheet(Reference Sheet) the criterea for the rows are as follows: =countifs STATUS Column is "Discharged" or "ACTIVE" , Discharged/Nonadmit Column is Greater than 05/19/2023 and Date activated column is less than 05/27/2023 I…

  • #Incorrect Argument Set

    Good Morning, I've been trying to figure this formula out but I can't seem to get it. I need to sum any amount in the computed count column, match it with the AP tech for a specific month. I've tried swapping the columns around but nothing seems to work. What am I missing? =SUMIFS({011-AP Archive AP Tech}, [Primary…

  • I need help with a if formula to get the $ amount total if another column = a specific item.

    I want to make a formula in the Column "A Total" in the highlighted Cell, that will give me the Sum of $ from Grand Total Column if the A is in the Project column.