Auto filling a cell based on the entry in another cell

dave.mcpherson56751
dave.mcpherson56751 ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hi,

We have created a form to capture feedback from several stores we manage. How can i get the Store managers name to auto populate on my sheet based on the store name?

I dont want this on the form just on my sheet when the entry has been submitted.

Sorry i am really struggling with this and fear it is around Vlook up which i am also struggling with.

 

any help would be gratefully received

 

Dave Mac

Tags:
«1

Comments

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

    Hi Dave,

    There are several ways to do this. One way could be to have separate forms or links for each store with the information pre-filled. (you can hide it on the form)

    You could have a formula on the sheet that automatically fills in the information.

    I hope this helps you!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    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.

  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Hi Andree,

    firstly thank you for such a speedy response....wow!!

    having a seperate  form for each store is not going to work unfortunately as there are nearly 200 stores soon growing to 300.

    I suppose i am looking for a formula that if store 1 was chosen it would know to auto populate the managers name.

    hope that makes sense?

    Thank you

     

    Dave

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would need a VLOOKUP or INDEX/MATCH referencing a table. If your set up is as below (Column names are in BOLD)(Table column names do not contain spaces):

     

    Manager             Store                   ManagerTable              StoreTable

    Formula                Store 1                John                              Store 1

    Formula                Store 3                Mike                              Store 2

    Formula                Store 2                Steve                            Store 3

     

    =INDEX(ManagerTable:ManagerTable, MATCH(Store@row, StoreTable:StoreTable, 0))

     

    Once you have built the table, it will compare the store number from the form to the table and populate the manager's name that's next to it.

     

    Using INDEX/MATCH provides a lot of flexibility as you can add, delete, or change the table as needed without messing with the formula.

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

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

    Happy to help!

    Paul beat me to it!

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I just did this sometime in the past 24 hours for someone else but instead of store/name it was name/title. It was still fresh in my head, so I was able to get it out here pretty quickly. It was an almost Identical response including the layout. Just had to change a few words to be more case specific. Haha

     

    Craig: In case you stumble upon this... No. I did NOT copy/paste either. yeswink

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

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

    laugh

    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.

  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Hi Paul,

     

    i have this working now, thank you so much for your help it really is appreciated.

    Kind regards and have a lovely weekend

     

    Dave

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

  • Is there a way to auto fill forms?   When entering a 7-10 digit code, transposing numbers is more common than I thought it would be.  By auto-filling a form with "Product Name" after entering a number, it would give another verification.  Anyway round that?   Also, any chance of ever getting a two column form format to alleviate long forms, or allows for side-by-side entering?  

  • Can I reference an entire sheet to pull data from with the Index/Match? Example: I have one sheet with up to date information (automated to connect to jira) and another sheet which is used for planning. I'd like to query the entire Jira dump sheet and display the up to date info in specific columns in the planning sheet. Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You can so long as you do not exceed 100 distinct cross sheet references and no more than 25,000 inbound cells.

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

  • Does this work if you want to auto-populate a contact? So the match would be a contact within smartsheet.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Yes. This works with contact type columns. Just be sure that both the column containing the formula and the column referenced by the INDEX function are both of the contact type, and you should be good to go.

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

  • I have tried use the index match example above several times and I either get unmatched or unparsable results

    Help

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Tom Hicke

    Could you provide a little more information? It would help to see screen captures of both the source sheet (where you are looking to pull information from) and the destination sheet (where you are pasting the formula), but please block out any sensitive data.

    You may be getting Unpareseable if the column names are not identical to what's in your sheet(s), or if there's a typo within the formula. The NOMATCH error means that it was unable to find an identical match, so then we would need to look at the values you're searching for in order to see what might be causing this.

    Thanks!

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!