Substitute Function?

Allison_S
Allison_S ✭
edited 12/09/19 inSmartsheet Basics

Hello,

I have a reporting sheet that I have created for individuals within our company. The original formulas need to be adjusted with different initials for each person. This means each individual needs their own sheet to track their numbers/orders. The formula is this,

=COUNTIFS({WH2 - ARCHIVED Orders (2019 Bend) Range 28}, "12", {WH2 - ARCHIVED Orders (2019 Bend) Range 15}, "BM") + COUNTIFS({WH2 - ARCHIVED Orders (2019 Bend) Range 29}, "12", {WH2 - ARCHIVED Orders (2019 Bend) Range 18}, "BM") + COUNTIFS({WH2 - ARCHIVED Orders (2019 Bend) Range 30}, "12", {WH2 - ARCHIVED Orders (2019 Bend) Range 21}, "BM")

The initials in this example are "BM" and I need to replace "BM" with "SM" or "CT". Is there a way to mass replace the initials within the formula, in different equations, within cells? I've been attempting to use the Substitute function, but it doesn't seem to be working. Any assistance would be greatly appreciated!

Thanks again.

· Share on Facebook Share on Twitter

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Allison,

    If possible, I would have a specific cell or column with the initials and then point the formula to that cell or column when you want to reference the initials.

    Would that work?

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter
  • Allison_S
    Allison_S ✭

    Hi Andree,

    谢谢你的建议。首字母列is separate, I have a column for initials and a column for the date the job is completed. Being fed from the date column is the month/day/week columns which I use in this formula to see who completed what and when. The problem is that we have 15 employees and I need to adjust the initials for each person's reporting sheet and I was wondering if there was a way to substitute on initial for another faster than doing it manually 52 times for each work week of the month?

    Any additional ideas would be greatly appreciated!

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    What I meant was that you should replace the initials in the formula with the cell reference where the initials would be and then it will be much easier to maintain.

    Or am I not understanding what you want to do?

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I agree with Andree. Somewhere on each of the sheets you could put the employee's initials. For this example I am just going to say you added a new column named "Initials" for this and have put the initials in the cell of that new column in row 1 (just for example on how to do what Andree is suggesting).

    Wherever you have the initials, simply use@cell= Initials$1.

    If you replicate that column on all sheets and put the corresponding initials in row one of that column, the formula will automatically update itself to look for whatever initials are there instead of having to manually change them in multiple formulas across multiple sheets.

    thinkspi.com

    · Share on Facebook Share on Twitter
  • Allison_S
    Allison_S ✭

    Yes, I fully understand what you are both suggesting and that it a brilliant idea! Why did't I think of that!?! :-)

    Thank you both for all your help - this will simplify our reporting sheets for each person dramatically! I appreciate your help!

    Allison

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Sometimes the simple answers are the hardest to find. Haha

    Happy to help!yes

    thinkspi.com

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    I'm always happy to help!

    Have a fantastic weekend!

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter

Categories

I understand. Unfortunately, there is no other option that I see. One thing to make it easier for you to add the name is to have a helper column in the source sheet with a formula to identify duplicates. If there is a record with no duplicate (i.e., a new name added), you can get notified through a workflow and use that to add the name to your metric sheet.<\/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":106937,"type":"question","name":"Column shifting","excerpt":"Hi There, Currently, I have set up dynamic drop down in target sheet and the rule is the column index can not change (i.e. stay index 32) However, the column is shifting (i.e. from 32 to 57) but user claims they didn't move or change the column index. Is there any way we can figure out why this happening or any where we…","snippet":"Hi There, Currently, I have set up dynamic drop down in target sheet and the rule is the column index can not change (i.e. stay index 32) However, the column is shifting (i.e.…","categoryID":321,"dateInserted":"2023-06-27T07:31:13+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T08:59:15+00:00","insertUserID":161133,"insertUser":{"userID":161133,"name":"Man Chui","url":"https:\/\/community.smartsheet.com\/profile\/Man%20Chui","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T09:36:58+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-28T10:28:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":28,"score":null,"hot":3375795628,"url":"https:\/\/community.smartsheet.com\/discussion\/106937\/column-shifting","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106937\/column-shifting","format":"Rich","lastPost":{"discussionID":106937,"commentID":382758,"name":"Re: Column shifting","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382758#Comment_382758","dateInserted":"2023-06-28T08:59:15+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-28T10:28:00+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T06:56:02+00:00","dateAnswered":"2023-06-27T12:25:56+00:00","acceptedAnswers":[{"commentID":382510,"body":"

This should be present from the activity log.<\/p>


<\/p>


<\/p>

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


<\/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":106975,"type":"question","name":"Case sensitivity when using data mesh","excerpt":"I set up a Data Mesh that is working quite well. Nurses enter their credential information and we use DataMesh to add other human resources information of record - including manager's email. The DataMesh is keying of of the common field of User ID. We noticed that for many of the nurses the match didn't work and also…","snippet":"I set up a Data Mesh that is working quite well. Nurses enter their credential information and we use DataMesh to add other human resources information of record - including…","categoryID":343,"dateInserted":"2023-06-27T19:39:23+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T20:09:03+00:00","insertUserID":114017,"insertUser":{"userID":114017,"name":"Jacqueline Ennis","url":"https:\/\/community.smartsheet.com\/profile\/Jacqueline%20Ennis","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxZA0IOt6rrnETj3_ATzn0g7JjbJTEyOyIfrNKKb=s96-c","dateLastActive":"2023-06-27T20:08:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":114017,"lastUser":{"userID":114017,"name":"Jacqueline Ennis","url":"https:\/\/community.smartsheet.com\/profile\/Jacqueline%20Ennis","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxZA0IOt6rrnETj3_ATzn0g7JjbJTEyOyIfrNKKb=s96-c","dateLastActive":"2023-06-27T20:08:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3375792506,"url":"https:\/\/community.smartsheet.com\/discussion\/106975\/case-sensitivity-when-using-data-mesh","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106975\/case-sensitivity-when-using-data-mesh","format":"Rich","lastPost":{"discussionID":106975,"commentID":382691,"name":"Re: Case sensitivity when using data mesh","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382691#Comment_382691","dateInserted":"2023-06-27T20:09:03+00:00","insertUserID":114017,"insertUser":{"userID":114017,"name":"Jacqueline Ennis","url":"https:\/\/community.smartsheet.com\/profile\/Jacqueline%20Ennis","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxZA0IOt6rrnETj3_ATzn0g7JjbJTEyOyIfrNKKb=s96-c","dateLastActive":"2023-06-27T20:08:06+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-27T20:09:23+00:00","dateAnswered":"2023-06-27T20:09:03+00:00","acceptedAnswers":[{"commentID":382691,"body":"

Perfect. Thank you so much!<\/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&categoryID=341&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":5419,"limit":3},"title":"Trending in Using Smartsheet","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Using Smartsheet

  • Orderable Charts

    Good morning, I've done several searches here but now I'm going in circles. I have a sheet with records of usage of certain scripts we have. Each row has the user name, the script they used and the time they saved by using each script. My goal is to create a dashboard with two bar charts: user name vs total time savings…

  • Column shifting

    Hi There, Currently, I have set up dynamic drop down in target sheet and the rule is the column index can not change (i.e. stay index 32) However, the column is shifting (i.e. from 32 to 57) but user claims they didn't move or change the column index. Is there any way we can figure out why this happening or any where we…

  • Case sensitivity when using data mesh

    I set up a Data Mesh that is working quite well. Nurses enter their credential information and we use DataMesh to add other human resources information of record - including manager's email. The DataMesh is keying of of the common field of User ID. We noticed that for many of the nurses the match didn't work and also…