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.
address formula
Comments
-
Richard Rymill SBP ✭✭✭✭✭✭
If you look at "Concatenation" in Smartsheet Help.http://help.smartsheet.com/customer/en/portal/articles/775363-using-formulas
-
looking to add the +
replace spaces and conna's with plus symble
-
J. Craig Williams ✭✭✭✭✭✭
Bob,
I assume the first result you have came from something like this:
=[AddressLine1]23 + "+" + City23 + "+" + State23 + "+" + PostCode23
and the second example is manual editing to show the result you desire.
Try this:
=SUBSTITUTE([AddressLine1]14 + " " + City14 + " " + State14 + " " + PostCode14, " ", "+")
Craig
-
Craig Thank you for your advice, you are correct in assumption. and yes I came up with =SUBSTITUTE([Street Address]114, " ", "+")
but I wasnt able to link the two steps together, to facilitate added couple columns and two stepped it.
=[fixed address]107 + "+" + City107 + "+" + State107 + "+" + Zip107. formula configure address with url infront to take to map or navigator from row in sheet or report.
www.google.com/maps/place/11712+W+Lark+Dr,+Chandler,+AZ+85287
I did try your example I am not getting something right. However we are functioning with the extra steps.
-
J. Craig Williams ✭✭✭✭✭✭
This works for me:
I do the substitute and addition of the URL in the same cell.
="www.google.com/maps/place/" + SUBSTITUTE([AddressLine1]14 + " " + City14 + " " + State14 + " " + PostCode14, " ", "+")
If it still doesn't work, you could share the sheet to me
and I'll take a look.
Craig
-
J. Craig Williams ✭✭✭✭✭✭
Update:
Bob shared the sheet with me.
I implemented the change above in a new column and suggested deletion of the previous four columns that were being used to pull the information together one piece at a time.
Craig
Categories
<\/p>
Current options are only to take screenshot or print the dashboard as a PDF or to One Note. However, there already is a planned enhancement in the roadmap to send the dashboard snapshot via a scheduled email (similar to reports). No timelines have been mentioned as yet.<\/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":[]},{"discussionID":107055,"type":"question","name":"Fill in project number based on ancestor level","excerpt":"I want to auto fill the project_number between the the Purple and Red rows. So all the children of Purple Project have a Project_Number = 4 and all the Red Children have a Project_number of 7 Tried using something like this. So if the Project is checked return the Project_Number. This give me \"UNPARSABLE\"…","snippet":"I want to auto fill the project_number between the the Purple and Red rows. So all the children of Purple Project have a Project_Number = 4 and all the Red Children have a…","categoryID":322,"dateInserted":"2023-06-28T20:28:32+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T12:31:20+00:00","insertUserID":124423,"insertUser":{"userID":124423,"name":"Julie Fleming","url":"https:\/\/community.smartsheet.com\/profile\/Julie%20Fleming","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T12:31:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":124423,"lastUser":{"userID":124423,"name":"Julie Fleming","url":"https:\/\/community.smartsheet.com\/profile\/Julie%20Fleming","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T12:31:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":21,"score":null,"hot":3376027792,"url":"https:\/\/community.smartsheet.com\/discussion\/107055\/fill-in-project-number-based-on-ancestor-level","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107055\/fill-in-project-number-based-on-ancestor-level","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107055,"commentID":383057,"name":"Re: Fill in project number based on ancestor level","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383057#Comment_383057","dateInserted":"2023-06-29T12:31:20+00:00","insertUserID":124423,"insertUser":{"userID":124423,"name":"Julie Fleming","url":"https:\/\/community.smartsheet.com\/profile\/Julie%20Fleming","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T12:31:11+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\/MQEUGWPAA2YV\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-29T12:31:08+00:00","dateAnswered":"2023-06-28T23:12:12+00:00","acceptedAnswers":[{"commentID":382982,"body":"
Hi Julie,<\/p>
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":107046,"type":"question","name":"How to add project dashboard link to project metadata sheet when using PMO template","excerpt":"Need some help here. Set up the PMO using the PMO template. PMs have added projects but the project dashboard hyperlink never populated in the intake sheet nor the project metadata. Trying to add that in now, as the project plan is already created. I've tried to hyperlink the project dashboard URL, copy\/past the URL in the…","snippet":"Need some help here. Set up the PMO using the PMO template. PMs have added projects but the project dashboard hyperlink never populated in the intake sheet nor the project…","categoryID":343,"dateInserted":"2023-06-28T18:45:39+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T12:11:09+00:00","insertUserID":162917,"insertUser":{"userID":162917,"name":"swaters","url":"https:\/\/community.smartsheet.com\/profile\/swaters","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T18:54:58+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":11,"score":null,"hot":3376020408,"url":"https:\/\/community.smartsheet.com\/discussion\/107046\/how-to-add-project-dashboard-link-to-project-metadata-sheet-when-using-pmo-template","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107046\/how-to-add-project-dashboard-link-to-project-metadata-sheet-when-using-pmo-template","format":"Rich","lastPost":{"discussionID":107046,"commentID":383038,"name":"Re: How to add project dashboard link to project metadata sheet when using PMO template","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383038#Comment_383038","dateInserted":"2023-06-29T12:11:09+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":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-29T10:26:58+00:00","dateAnswered":"2023-06-28T18:54:05+00:00","acceptedAnswers":[{"commentID":382940,"body":" Right click on the cell, select \"Hyperlink\", select \"Link to other Smartsheet\", click on \"select sheet>\", then select the dashboard to want to create the hyperlink to. You can also adjust the displayed text if needed.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","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&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":[]}">