Auto filling a cell based on the entry in another cell

09/27/18 Edited 12/09/19

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

Popular Tags:

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 PARTNER & CONSULTANT / EXPERT

    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.

  • 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 NewcomePaul 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([email protected], 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.

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

    Happy to help!

    Paul beat me to it!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    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.

  • Paul NewcomePaul 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

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

    laugh

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    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.

  • 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 NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

  • 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 NewcomePaul Newcome ✭✭✭✭✭

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

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

  • Paul NewcomePaul 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.

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

    Help

  • 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

Sign In or Register to comment.