Enhancement Request: Sheet/Workspace Level References in Formulas
I would like to create notifications for the creation of new sheets in specific workspaces and in general better track what sheets are getting created where. To this point I would like to build a master sheet with drop downs for each workspace showing what folders and sheets are in them. A directory if you will.
This would allow me to build notifications when new sheets are added to workspaces, and have a master smartsheet tracker available to show what my team is working on. Right now our setup is entirely manual, everyone has to go to the project sheet and edit it, and it's just another step everyone has to take when building a sheet, and because of that sometimes the project sheet gets missed when a sheet is created.
Comments
-
Shaine Greenwood EmployeeHi Luke,
When you have a moment, please submit an enhancement request using the Product Enhancement Request form to the right of the community site. Your feedback will go straight to our Product team for review.
With that being said, you might consider using our API to create this automation if you have developer resources available at your organization. More information on this is available here:https://smartsheet-platform.github.io/api-docs/
-
J. Craig Williams ✭✭✭✭✭✭If you have resources and no developer, contact me.
Craig
-
L_123 ✭✭✭✭✭✭
I know about the API and am currently working on learning RUBY in order to utilize the API. That said I think it would be good to not have to use the API for this functionality, which is why I suggested it.
I submitted a product enhancement request at the same time I made this post.
-
L_123 ✭✭✭✭✭✭
Thanks for the offer, but I don't think we are ready to outsource any of our development at this point. We are still working on building our team of key users and developing internal skills. Maybe once our team gets more comfortable with the program we can start to use the API and hire external developers, in fact I am hoping for that and building my own skills in preparation for that future. The big thing we would want to do is link smartsheet to SAP to reduce the number of times we are inputting information.
Until then I put this suggestion up because I think it would be a very useful tool for users who aren't programmers.
Help Article Resources
Categories
If your primary column is always going to have the text \"#...\" at the end of the field for the first parent row in each group, you should be able to use the \"#\" as a starting point to reference in a RIGHT function. In this case, the below formula should work as a column formula in the Project_Number column. You will just need to substitute your column name \"Project\\Epic\\Feature\\Sprint Objectives\\Task\" wherever it says Primary Column.<\/p>
=IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, RIGHT([Primary Column]@row, LEN([Primary Column]@row) - FIND(\"#\", [Primary Column]@row)), PARENT([Project_Number]@row))<\/p>
Using the LEN - FIND functions should keep the formula working correctly as you get into multiple-digit project numbers. Keep in mind that this formula will need to be updated if the text \"#...\" is ever omitted\/changed in the primary column or if it is not the rightmost text in the field.<\/p>
Hope this works for you! 😊<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107045,"type":"question","name":"IF AND\/OR plus a VLOOKUP","excerpt":"I'm having trouble getting a formula correct; I keep getting unparsable or circular reference. I'm wanting to add the name of our rep based on the status of two columns. If the CRM Portfolio is checked AND the Jurisdiction = Federal, I want to insert the name \"Jason\". If the CRM Portfolio is checked and the Jurisdiction is…","snippet":"I'm having trouble getting a formula correct; I keep getting unparsable or circular reference. I'm wanting to add the name of our rep based on the status of two columns. If the…","categoryID":322,"dateInserted":"2023-06-28T18:44:17+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T12:10:45+00:00","insertUserID":118833,"insertUser":{"userID":118833,"name":"Patti Hammonds","url":"https:\/\/community.smartsheet.com\/profile\/Patti%20Hammonds","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pFFY6XW-qfo!8mdfd1RJ8jo!9etB_NquGxb","dateLastActive":"2023-06-28T18:51:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-29T12:50:07+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":21,"score":null,"hot":3376020302,"url":"https:\/\/community.smartsheet.com\/discussion\/107045\/if-and-or-plus-a-vlookup","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107045\/if-and-or-plus-a-vlookup","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107045,"commentID":383037,"name":"Re: IF AND\/OR plus a VLOOKUP","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383037#Comment_383037","dateInserted":"2023-06-29T12:10:45+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-29T12:50:07+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\/431QLFVXWSYV\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-29T10:23:55+00:00","dateAnswered":"2023-06-28T18:51:53+00:00","acceptedAnswers":[{"commentID":382939,"body":" Give this a try:<\/p> =IF([CRM Portfolio]@row = 1, IF(Jurisdiction@row = \"Federal\", \"Jason\", VLOOKUP(State@row, {CL_States Range 2}, 2, false)))<\/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":107038,"type":"question","name":"Modified Date loses detail when referenced","excerpt":"Application: Trying to capture and display a 'sheet last modified' value on a dashboard. Approach: Using a formula in the sheet summary sidebar to find the max value of all timestamps in the 'Modified' column. Formula is as follows and is functioning as expected. =MAX([Modified]:[Modified]) Problem: The displayed value…","snippet":"Application: Trying to capture and display a 'sheet last modified' value on a dashboard. Approach: Using a formula in the sheet summary sidebar to find the max value of all…","categoryID":322,"dateInserted":"2023-06-28T17:43:23+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T12:25:55+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T21:44:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-29T12:50:07+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":31,"score":null,"hot":3376018158,"url":"https:\/\/community.smartsheet.com\/discussion\/107038\/modified-date-loses-detail-when-referenced","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107038\/modified-date-loses-detail-when-referenced","format":"Rich","lastPost":{"discussionID":107038,"commentID":383050,"name":"Re: Modified Date loses detail when referenced","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383050#Comment_383050","dateInserted":"2023-06-29T12:25:55+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-29T12:50:07+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-28T21:29:15+00:00","dateAnswered":"2023-06-28T18:46:15+00:00","acceptedAnswers":[{"commentID":382932,"body":" Set the Sheet Summary field as text\/number then add +\"\" to the end of the MAX function (plus quote quote) to convert it into a text string.<\/p> =MAX([Modified]:[Modified]) + \"\"<\/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":[]}">
