Auto filling a cell based on the entry in another cell
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
Comments
-
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: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.
-
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
-
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.
-
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: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.
-
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.
-
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.
-
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
-
-
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!
-
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.
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!