Auto Fill a row from another row

Minni
Minni ✭✭
in公式s and Functions

I am trying to autofill a row with a specific number when a certain drop down in used in another column but I am getting an error. I am not seeing an Automation or a conditional format that supports this so I am assuming that this will require a function but I am not able to figure out which one.

IE- When FY23 is chosen under project name, I want Account Number to auto fill account number 123-456-789 and when FY24 is chosen I want account number to auto fill 987-654-321

Can anyone assist with this one?

Capture.JPG


Minni Florence

Senior Project Coordinator

Industrial Properties Construction

· Share on Facebook Share on Twitter

Best Answer

  • Ramzi
    Ramzi ✭✭✭✭
    Answer ✓

    Try this -

    In your Account Number (CPR) field, use this formula:

    =IF([Project Name]@row = "FY23", "123-456-789", IF([Project Name]@row = "FY24", "987-654-321",""))

    If you have a lot of values, you may want to consider a reference sheet and then INDEX/MATCH into that sheet.

    I hope this helps you. Have a great day!

    Ramzi

    内容的解决方案架构师

    www.adapture.com

    · Share on Facebook Share on Twitter

Answers

  • Ramzi
    Ramzi ✭✭✭✭
    Answer ✓

    Try this -

    In your Account Number (CPR) field, use this formula:

    =IF([Project Name]@row = "FY23", "123-456-789", IF([Project Name]@row = "FY24", "987-654-321",""))

    If you have a lot of values, you may want to consider a reference sheet and then INDEX/MATCH into that sheet.

    I hope this helps you. Have a great day!

    Ramzi

    内容的解决方案架构师

    www.adapture.com

    · Share on Facebook Share on Twitter
  • Minni
    Minni ✭✭

    @RamziYou are my HERO!!! Thank you so very very much!! It worked!!

    Minni Florence

    Senior Project Coordinator

    Industrial Properties Construction

    · Share on Facebook Share on Twitter
  • Ramzi
    Ramzi ✭✭✭✭

    Happy to help!

    Cheers,

    Ramzi

    内容的解决方案架构师

    www.adapture.com

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

公式combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式Handbook template!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
=COUNT(DISTINCT([Employee]:[Employee]))<\/p>

Replace [Employee] if your column has a difference name. This will essentially generate a list of distinct entries and then count them.<\/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":109307,"type":"question","name":"Show approval status based on # of approvals vs. denials","excerpt":"I'm looking for a formula that will show \"Yes\", \"Hold\", or \"No\" based on # of approvals. There are 6 total approvals needed, if all 6 approve, then the Approved column would show \"Yes\". If there is 1 or more denials, then the Approved column would show \"No\". If the total of the columns don't equal to 6, then the Approved…","snippet":"I'm looking for a formula that will show \"Yes\", \"Hold\", or \"No\" based on # of approvals. There are 6 total approvals needed, if all 6 approve, then the Approved column would show…","categoryID":322,"dateInserted":"2023-08-22T21:20:12+00:00","dateUpdated":null,"dateLastComment":"2023-08-22T22:04:40+00:00","insertUserID":96646,"insertUser":{"userID":96646,"name":"@SPark","url":"https:\/\/community.smartsheet.com\/profile\/%40SPark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!co_Wh_2S-n4!ZAPZGbmgZrw!TRZYwfjvFdi","dateLastActive":"2023-08-22T22:57:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":96646,"lastUser":{"userID":96646,"name":"@SPark","url":"https:\/\/community.smartsheet.com\/profile\/%40SPark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!co_Wh_2S-n4!ZAPZGbmgZrw!TRZYwfjvFdi","dateLastActive":"2023-08-22T22:57:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3385482292,"url":"https:\/\/community.smartsheet.com\/discussion\/109307\/show-approval-status-based-on-of-approvals-vs-denials","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109307\/show-approval-status-based-on-of-approvals-vs-denials","format":"Rich","lastPost":{"discussionID":109307,"commentID":392022,"name":"Re: Show approval status based on # of approvals vs. denials","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392022#Comment_392022","dateInserted":"2023-08-22T22:04:40+00:00","insertUserID":96646,"insertUser":{"userID":96646,"name":"@SPark","url":"https:\/\/community.smartsheet.com\/profile\/%40SPark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!co_Wh_2S-n4!ZAPZGbmgZrw!TRZYwfjvFdi","dateLastActive":"2023-08-22T22:57:22+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\/AJ5OBZXXQRAU\/2820-29-intake-smartsheet-com-2023-08-22-14-16-38.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"(20) Intake - Smartsheet.com 2023-08-22 14-16-38.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-22T22:04:29+00:00","dateAnswered":"2023-08-22T21:36:22+00:00","acceptedAnswers":[{"commentID":392011,"body":"

Something like this: =IF([Approval Count]@row = 6, \"Yes\", IF([Denied Count]@row >= 1, \"No\", \"Hold\"))<\/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":[]},{"discussionID":109287,"type":"question","name":"Formula help","excerpt":"=IF([WD Schematic End]@row = \"n\/a\", 0, IF([WD Schematic End]@row > [STR Schematic End]@row, 1, 0)) Above formula meant to trigger a red flag if the WD & STR Schematic End dates do not match. The formula I wrote is pulling back 'invalid' ... I am missing something and cannot figure out what!","snippet":"=IF([WD Schematic End]@row = \"n\/a\", 0, IF([WD Schematic End]@row > [STR Schematic End]@row, 1, 0)) Above formula meant to trigger a red flag if the WD & STR Schematic End dates do…","categoryID":322,"dateInserted":"2023-08-22T17:45:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-22T18:30:00+00:00","insertUserID":157887,"insertUser":{"userID":157887,"name":"holli.nunn","url":"https:\/\/community.smartsheet.com\/profile\/holli.nunn","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T18:29:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":157887,"lastUser":{"userID":157887,"name":"holli.nunn","url":"https:\/\/community.smartsheet.com\/profile\/holli.nunn","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T18:29:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":28,"score":null,"hot":3385456532,"url":"https:\/\/community.smartsheet.com\/discussion\/109287\/formula-help","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109287\/formula-help","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109287,"commentID":391947,"name":"Re: Formula help","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391947#Comment_391947","dateInserted":"2023-08-22T18:30:00+00:00","insertUserID":157887,"insertUser":{"userID":157887,"name":"holli.nunn","url":"https:\/\/community.smartsheet.com\/profile\/holli.nunn","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T18:29:36+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\/DLAV2H53BW86\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-22T18:29:34+00:00","dateAnswered":"2023-08-22T18:25:02+00:00","acceptedAnswers":[{"commentID":391943,"body":"

Give this a try:<\/p>

=IF([WD Schematic End]@row = \"n\/a\", 0, IF([WD Schematic End]@row <> [STR Schematic End]@row, 1, 0))<\/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&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":[]}">

Trending in Formulas and Functions

  • Counting people

    Hi Smartsheet Community I use smartsheet to manage volunteer opportunities for my organization. One sheet details the employee, event, date, etc. for the whole year. I want to find out how many total employees volunteer without having to go through the entire list and cross out duplicates. Any tips on how to do that? thank…

  • Show approval status based on # of approvals vs. denials

    I'm looking for a formula that will show "Yes", "Hold", or "No" based on # of approvals. There are 6 total approvals needed, if all 6 approve, then the Approved column would show "Yes". If there is 1 or more denials, then the Approved column would show "No". If the total of the columns don't equal to 6, then the Approved…

  • 公式help

    =IF([WD Schematic End]@row = "n/a", 0, IF([WD Schematic End]@row > [STR Schematic End]@row, 1, 0)) Above formula meant to trigger a red flag if the WD & STR Schematic End dates do not match. The formula I wrote is pulling back 'invalid' ... I am missing something and cannot figure out what!