Auto filling a cell based on the entry in another cell

dave.mcpherson56751

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

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

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

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.

Andrée Starå

Happy to help!

Paul beat me to it!

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. 

Andrée Starå

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

Happy to help! 

tom.rutter20641

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?  

FactionPMO

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

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

ShawnRHogue

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

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.

Tom Hicke

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

Help

Genevieve P.

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