Autofill a column based on another column value

I have one column set up with a dropdown menu and want another column to autofill with a specific value tied to each dropdown menu selection. Is this possible?

Tags:

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 08/15/18

    Hi Dana,

    Yes, it is. The formula below is one way to structure it. Can you share the sheet or a screenshot because that will make it much easier to help you?

    https://help.smartsheet.com/function/if

    I hope this helps you!

    Best,

    Andrée Starå - Workflow Consultant @ Get Done

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Thank you! I was on the right track but I rarely use functions and am not sure how to set it up. I'd like to autofill an amount in the Cost column based off the dropdown selection in the Media Type column.

     

    Capture.JPG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    =IF([Media Type]@row = "Enter first statement here", 50, IF([Media Type]@row = "Second statement", 95.5, IF([Media Type]@row = "Third statement", 125.25.................)))

     

    BOLD sections are where you type in the option you are referring to. (Case sensitive)

    UNDERLINED sections are where you enter the price corresponding to the BOLD section right before it.

     

    When nesting IF statements, try to think of it like this...

     

    =IF(That other cell = "This Text", Then enter this amount, IF(That other cell = "THIS Text, Then enter this different amount, IF(..........

     

    To close it out, your final IF statement would look something like this:

     

    ........... , IF([Media Type]@row = "Final statement in list", price, ""))))))))

     

    The double quotes at the end will leave the cell blank if there is no type selected.

     

    To determine how many closing parenthesis you need, simply count how many times you typed IF and put that many on the end. So in the screenshot above you have 8 different options. Therefore you will have 8 IF statements (one for each option) which means after the double quotes you will use 8 closing parenthesis. ))))))))

     

    If you have the Cost column formatted for currency, then all you need in your formula for the price is the actual number. If it is an even amount (no change), you don't even have to put the .00 on the end.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    I would NOT use a NestedIF for this purpose. Instead I would use either LOOKUP() or INDEX(...,MATCH(...)) functionality.

    https://help.smartsheet.com/function/vlookup.

    My primary reason is visibility. And ease of maintenance. My primary reasons are visibility and ease of maintenance. And a fanatical devotion to the Pope. My three main reasons are ... 

    Both the NestedIF and table methods have the same flaw (imo), in that if the cost changes, something sold before the change will have the new cost in the record. 

    That's a post for another time.

    Craig

     

  • Bill R
    Bill R ✭✭

    Hi,

    I am having trouble with my formula to return text based on a number in another column.

    =IF([Defect]@row “1”, “Bad Bottle”, "")

    Not sure what I am doing wrong. Unfortunately LOOKUP will not be an option here.

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

    Hi @Bill R

    I hope you're well and safe!

    Try something like this.

    =IF([Defect]@row= 1, "Bad Bottle", "")

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic weekend!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!