Auto Fill Fields in Sheet

Hello,

I have 2 columns I am looking to auto populate based on what is selected in another column.

Depending on the requestors name the Manager and Director will populate. The thing I am having trouble with is that multiple requestors are under a manager. I cant get my IF statement to work.

Example:

Lisa, Doug, and Frank are all under Michael as the Manager.

I can get =if Requestor is Lisa then add Michael to the column, but I cant get multiple selections... Also, all fields are Contacts.

The purpose is to be able to remove those two fields from the form.

Best Answers

Answers

  • Thank you for your comments. After posting I watched tons of YouTube videos and saw that a Vlookup to another table was the easiest.

    The columns are populating appropriately depending on the requestor field. I am now noticing that dragging the formula down is creating blank rows. so I do a form submission, and the fields are populating, but once I delete the "test" the formula is no longer there.  Do I have to leave my test submissions in order for the formula to continue throughout the sheet?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    For auto-fill, you need to have at least 2 rows of the same hierarchy and containing the formula above and/or below the new row.

    You would not drag the formula down. It sounds as if your forms are populating at the bottom of the sheet, so you would leave two "formatting" rows at the top of your sheet. The only columns that NEED to have anything in them are those columns containing the formulas you want to auto-fill.

    I generally fill the rest of the columns with "FORMATTING ROW - DO NOT DELETE OR EDIT" if other people are going to be accessing the sheet directly.

    Once you have had at least two form entries populate that you are not going to delete for the sheet, you can then delete your two formatting rows and the formula should carry on.

  • @Paul Newcome

    That was EXTREMLY helpful. Thank you SO much!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️

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

    @Jessica Langston,

    I saw that Paul answered already!

    Glad you got it working!

    Let me know if I can help with anything else!

    Best, 

    Andrée 

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    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 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!