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.
ideas for department wide status report
The director of our department wants to use Smartsheet to consolidate status reports across multiple managers showing the latest status update for a Task(Row).
We could do this in one sheet, but we want to be able to have historic (older) updates for the Task stay on the sheet.
My idea is for each mnager to have his/her own sheet with a list of all the projects they are managing. When they update a Project/Task, they will copy the latest row and insert and identical row just above the previous udpate. They will then make the necessary udpates and set the "Updated Date" to the current date.
To get the latest data from multiple managers, I was then thinking of creating a report that would capture the latest update by date for each of the manager's projects/tasks and summarive into one overall deaprtmental report.
Any other suggestios that have worked for others?
通讯ents
-
斯科特豆 EmployeeHi Tim,
Here's another simple idea you might try: add a new "latest update" checkbox column in your sheet, and build your director's report to include only those rows. Your managers check a row and your director's report shows only those items, regardless of last time they were updated. If a manager is on vacation, you'd still capture their last update from prior week.
-- Scott
-
Tim Meeks ✭✭✭✭✭✭
as an update, we didn't need the Update Date as a condition to pull on. We are just pulling the Project status row regardless of the date it was udpated.
Categories
HAS() will only return true under three conditions.<\/p>
- The cell is a multicontact cell and contains a match for the contact<\/li>
- The cell is a multiselect dropdown and contains a match<\/li>
- The cell is not a multicontact or multiselect dropdown and contains ONLY and EXACTLY the text you are matching against. <\/li><\/ol>
I am assuming your row in question is a standard Text\/Number cell? If so, you will need to get somewhat creative with some CONTAINS() statements.<\/p>
This will narrow down your matches to only cells that contain a space both before and after TAC. If, however, TAC can appear as the first or last \"word\" in the cell, it would not match in that circumstance as there would only be a space on one side.<\/p>
=IF(CONTAINS(\" TAC \", [Column Name1]@row), 1, 0)<\/p>
<\/p>If TAC can appear as the first or last \"word\" in the cell, this will cover those possibilities as well.<\/p>
=IF(OR(CONTAINS(\" TAC \", [Column Name1]@row), LEFT([Column Name1]@row, 4) = \"TAC \", RIGHT([Column Name1]@row, 4) = \" TAC\"), 1, 0)<\/p>
<\/p>If it is possible that TAC will be the exact entry of the cell, you will need an additional statement to cover that, as there would not be spaces on either side. This will add that option.<\/p>
=IF(OR(CONTAINS(\" TAC \", [Column Name1]@row), LEFT([Column Name1]@row, 4) = \"TAC \", RIGHT([Column Name1]@row, 4) = \" TAC\", [Column Name1]@row = \"TAC\"), 1, 0)<\/p>
<\/p>If there are other situations you may have in your sheet, i.e., TAC inside parenthesis, or before or after punctuation, etc, you will additional statements to include those options 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"}]},{"discussionID":109146,"type":"question","name":"Identify Workflow from ID?","excerpt":"Good morning, got a random one. Is there any way to identify what sheet or workflow an automation ran from? I got a rogue automation this morning, and I don't know what sheet it lives in. However, I noticed at the bottom of the email, there is an ID number, similar to a sheet or row ID. Is there any way, maybe with the…","snippet":"Good morning, got a random one. Is there any way to identify what sheet or workflow an automation ran from? I got a rogue automation this morning, and I don't know what sheet it…","categoryID":321,"dateInserted":"2023-08-18T14:25:47+00:00","dateUpdated":null,"dateLastComment":"2023-08-19T08:29:31+00:00","insertUserID":137019,"insertUser":{"userID":137019,"name":"Samuel Mueller","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Samuel%20Mueller","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B9G8B70WXQWC\/nKTI46EH9WQOM.jpg","dateLastActive":"2023-08-18T20:30:29+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers"},"updateUserID":null,"lastUserID":161820,"lastUser":{"userID":161820,"name":"Hamza1","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Hamza1","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-20T04:58:20+00:00","banned":0,"punished":0,"private":false,"label":"Moderator"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":50,"score":null,"hot":3384804918,"url":"https:\/\/community.smartsheet.com\/discussion\/109146\/identify-workflow-from-id","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109146\/identify-workflow-from-id","format":"Rich","tagIDs":[227,334,448],"lastPost":{"discussionID":109146,"commentID":391519,"name":"Re: Identify Workflow from ID?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391519#Comment_391519","dateInserted":"2023-08-19T08:29:31+00:00","insertUserID":161820,"insertUser":{"userID":161820,"name":"Hamza1","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Hamza1","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-20T04:58:20+00:00","banned":0,"punished":0,"private":false,"label":"Moderator"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/EYR2B79VH1AY\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-18T19:37:53+00:00","dateAnswered":"2023-08-18T19:10:30+00:00","acceptedAnswers":[{"commentID":391468,"body":"
Yeah. That does sound like it could take awhile. Haha. And if you knew the sheet I'd, you'd be able to just jump to it in SS instead of needing the api.<\/p>
<\/p>Maybe a product enhancement request to expand the workflow portion of the api to include the sheet id is in order.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":227,"urlcode":"api-and-developers","name":"API and Developers"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":448,"urlcode":"workflows-in-smartsheet","name":"Workflows in Smartsheet"}]}],"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":[]}">
