Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, pleaseVisit the Current Forums.

Combined Reports

Doug Heinrich
Doug Heinrich ✭
edited 12/09/19 inArchived 2015 Posts

I have a combined report that is pulling information from 10 different smartsheets. The columnar information is the same from each of the smartsheets. I have it grouping by the smartsheet the information is coming from, but I would like the "title" of the smartsheet to show up at the beginning of the data from each sheet.

Thoughts on how this might be accomplished?

Tags:
· Share on Facebook Share on Twitter

Comments

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭

    1) At the top of the task column for each sheet, enter the name of the sheet.

    2) Make sure to populate a column that would trigger the selection for the report. You could even add a hidden column to accomplish this. And include that hidden column in ther report criteria.

    That is what I do for report headers

    Additionally, you can add a hyperlink to the cell that contains the name of the sheet in step one. This provides the reader of the report a link back to the project sheet if desired.

    I hope that helps,

    Brett

    · Share on Facebook Share on Twitter
  • Doug Heinrich
    Doug Heinrich ✭
    edited 10/02/15

    That does help. Thanks

    我还发现你可以使头poarent task to all the other ones. That always keeps it on top too.

    · Share on Facebook Share on Twitter
  • John Hammond
    John Hammond ✭

    I may be misreading the question, but reports by default have a "sheet name" column. Is that what you are looking for?

    · Share on Facebook Share on Twitter
  • Doug Heinrich
    Doug Heinrich ✭

    I am looking for a "header" of sorts, only prints out above all the projects.

    1. OFFICE A SCHEDULE
    2. Project Number 1
    3. Project Number 2
    4. Project Number 3
    5. etc...

    not a separate "sheet name" on each line.

    · Share on Facebook Share on Twitter
This discussion has been closed.

Categories

Nevermind, I resolved it!<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":335,"urlcode":"sheet-summary","name":"Sheet Summary"}]},{"discussionID":107817,"type":"question","name":"How to get the formulas to correctly count the completed tasks?","excerpt":"The current formula counts every task for the total. =COUNTIF(DESCENDANTS([Planning Done]@row), 1) + \" of \" + COUNT(CHILDREN([Task\/Topic]@row)) + \" Done\" I have been trying to get it to only count when all subtask from its proper row have been completed. There are 4 tasks and over 60 subtasks. When the subtasks are…","snippet":"The current formula counts every task for the total. =COUNTIF(DESCENDANTS([Planning Done]@row), 1) + \" of \" + COUNT(CHILDREN([Task\/Topic]@row)) + \" Done\" I have been trying to get…","categoryID":322,"dateInserted":"2023-07-19T19:04:08+00:00","dateUpdated":"2023-07-19T19:16:18+00:00","dateLastComment":"2023-07-19T19:40:18+00:00","insertUserID":163690,"insertUser":{"userID":163690,"name":"Ruvalcaba12","url":"https:\/\/community.smartsheet.com\/profile\/Ruvalcaba12","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-19T20:44:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":163690,"lastUserID":163690,"lastUser":{"userID":163690,"name":"Ruvalcaba12","url":"https:\/\/community.smartsheet.com\/profile\/Ruvalcaba12","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-19T20:44:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":41,"score":null,"hot":3379592666,"url":"https:\/\/community.smartsheet.com\/discussion\/107817\/how-to-get-the-formulas-to-correctly-count-the-completed-tasks","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107817\/how-to-get-the-formulas-to-correctly-count-the-completed-tasks","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107817,"commentID":386077,"name":"Re: How to get the formulas to correctly count the completed tasks?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386077#Comment_386077","dateInserted":"2023-07-19T19:40:18+00:00","insertUserID":163690,"insertUser":{"userID":163690,"name":"Ruvalcaba12","url":"https:\/\/community.smartsheet.com\/profile\/Ruvalcaba12","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-19T20:44:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"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\/2N6H1QH5FWDV\/screenshot.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"ScreenShot.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-19T19:24:31+00:00","dateAnswered":"2023-07-19T19:19:24+00:00","acceptedAnswers":[{"commentID":386066,"body":"

@Ruvalcaba12<\/a> try his<\/p>

Create a helper column called \"Helper\" and put this column formula<\/p>

=IF(COUNT(CHILDREN([Planning Done]@row)) = 0, \"//www.santa-greenland.com/community/discussion/comment/\", IF(COUNT(CHILDREN([Planning Done]@row)) = COUNTIF(CHILDREN([Planning Done]@row), 1), 1, 0))<\/p>

Then in your top level put this formula<\/p>

=COUNTIFS(CHILDREN(Helper@row), 1) + \" of \" + COUNT(CHILDREN()) + \" Done\"<\/p>"},{"commentID":386074,"body":"

@Ruvalcaba12<\/a> Maybe if you change the text of your second tier children to this or something similar<\/p>

=IF(COUNT(CHILDREN([Planning Done]@row)) = COUNTIF(CHILDREN([Planning Done]@row), 1), \"Complete\", COUNTIFS(CHILDREN([Planning Done]@row), 1) + \" of \" + COUNT(CHILDREN([Task\/Topic]@row)) + \" Done\")<\/p>

Then your top tier would be <\/p>

=COUNTIFS(CHILDREN(), \"Complete\") + \" of \" + COUNT(CHILDREN()) + \" Done\"<\/p>"},{"commentID":386075,"body":"

You could keep the number but you have to change something to distinguish. Second Tier:<\/p>

=IF(COUNT(CHILDREN([Planning Done]@row)) = COUNTIF(CHILDREN([Planning Done]@row), 1), COUNTIFS(CHILDREN([Planning Done]@row), 1) + \" of \" + COUNT(CHILDREN([Task\/Topic]@row)) + \" Complete\", COUNTIFS(CHILDREN([Planning Done]@row), 1) + \" of \" + COUNT(CHILDREN([Task\/Topic]@row)) + \" Done\")<\/p>

Top tier:<\/p>

=COUNTIFS(CHILDREN(), CONTAINS(\"Complete\", @cell)) + \" of \" + COUNT(CHILDREN()) + \" Done\"<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107785,"type":"question","name":"Formula Needed","excerpt":"Hi Smartsheet Team, I am looking for help with a formula: I have a column \"Schedule Health\" that has RYG Balls I am wanting this to change color based on certain criteria from 3 other columns \"Status\", \"Start Date\", \"End Date\". Ball is GREEN if \"Start Date\" is 7 days from today (ie project has not started yet) Ball is…","snippet":"Hi Smartsheet Team, I am looking for help with a formula: I have a column \"Schedule Health\" that has RYG Balls I am wanting this to change color based on certain criteria from 3…","categoryID":322,"dateInserted":"2023-07-19T12:29:48+00:00","dateUpdated":null,"dateLastComment":"2023-07-20T00:06:46+00:00","insertUserID":163658,"insertUser":{"userID":163658,"name":"Rod Sanderson","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Rod%20Sanderson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T00:07:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163658,"lastUser":{"userID":163658,"name":"Rod Sanderson","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Rod%20Sanderson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T00:07:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":29,"score":null,"hot":3379583194,"url":"https:\/\/community.smartsheet.com\/discussion\/107785\/formula-needed","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107785\/formula-needed","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107785,"commentID":386098,"name":"Re: Formula Needed","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386098#Comment_386098","dateInserted":"2023-07-20T00:06:46+00:00","insertUserID":163658,"insertUser":{"userID":163658,"name":"Rod Sanderson","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Rod%20Sanderson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T00:07:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-19T15:43:55+00:00","dateAnswered":"2023-07-19T13:51:54+00:00","acceptedAnswers":[{"commentID":385913,"body":"

Hey @Rod Sanderson<\/a> ,<\/p>

=IF(OR(AND(Status@row <> \"Complete\", [End Date]@row <= TODAY()), AND(Status@row = \"in progress\", [End Date]@row >= TODAY()), AND(Status@row = \"in progress\", [Start Date]@row <= TODAY())), \"Red\", 0)<\/p>

I think this should work for the red.<\/p>

Let me know if this is the direction you are looking for and I can write the rest of the colors as well.<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&siteSectionID=0&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 Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending Posts

  • COUNTIF Issue on Sheet Summary

    I am trying to see how many times Q1, Q2, Q3, and Q4 appear in my "Quarter" column on my sheet within my sheet summary. I am using the formula =COUNTIF([Quarter:Quarter], "Q1") to try to get just Q1 and it is not working. Any suggestions on why that is not working?

  • How to get the formulas to correctly count the completed tasks?

    The current formula counts every task for the total. =COUNTIF(DESCENDANTS([Planning Done]@row), 1) + " of " + COUNT(CHILDREN([Task/Topic]@row)) + " Done" I have been trying to get it to only count when all subtask from its proper row have been completed. There are 4 tasks and over 60 subtasks. When the subtasks are…

  • Formula Needed

    Hi Smartsheet Team, I am looking for help with a formula: I have a column "Schedule Health" that has RYG Balls I am wanting this to change color based on certain criteria from 3 other columns "Status", "Start Date", "End Date". Ball is GREEN if "Start Date" is 7 days from today (ie project has not started yet) Ball is…