Auto fill a second column based on a drop down selection using a form

Auto fill a second column based on a drop down selection using a form

I understand I can likely do this with a very, very long VLOOKUP  or IF/THEN formula in every cell, but I'm dealing with over a hundred items and that will be unwieldy. 

Situation:

I would like to have a form where someone selects one option from a dropdown of ~100 items. That value then populates into a sheet. (This seems to be the easy part.)

Once the dropdown value is on the sheet, I would like a linked value to populate in the column next to it. 

Example: Someone selects "Space Heater" from the dropdown menu on the form, that item populates into Column 1 of the sheet, and then Column 2 automatically fills in the amount "$75" 

I have a separate sheet for source data which lists all dropdown options in one column and all prices associated with it in another column, so this sheet could be referenced. 

I do not want this linked value (in this case the price) populating visibly on the form, as the people filling out the form would not be privy to sales prices. 

The practical application here is a person documenting our items they've brought to clients. We want to track what items are being dropped off, and what their sales price is. 

Any help would be greatly appreciated! Thank you.

Comments

  • Mike WildayMike Wilday ✭✭✭✭✭

    Your vlookup should actually be fairly short! I'll break it down for you... 

     

    =Vlookup([Dropdown Column Title]@row

    • this is looking at your dropdown column. Replace my verbiage with your actual title. 
    • After you type in the comma, look in the dropdown from the formula box, and choose reference data from another sheet. 
    • https://help.smartsheet.com/articles/2476606
    • Select your data source, and both columns for referencing. 
    • You should see something similar to the following. 

    =Vlookup([Dropdown Column Title]@row, {Your Reference Title}

    =Vlookup([Dropdown Column Title]@row, {Your Reference Title}, 2, false)

    • Now add a comma after the reference title and the number 2 since you want the 2nd column of your data to be retrieved. 
    • Add another comma and the word false which indicates the need for an exact match. Then close the formula. 

    https://help.smartsheet.com/function/vlookup

    Let me know if you have trouble getting it to work. You'll then want to grab the lower-right hand corner of the cell and drag it into the rest of your form entries. New entries should acquire the formula from the rows before it (or after if new entries appear at the top of the list). 

  • edited 11/28/18

    You could use this formula: =INDEX({Number List Range 3}, MATCH([Part Number]6, {Number List Range 2}, 0))

    {Number List Range 3} = Column with Prices

    [Part Number]6 = Cell with your dropdown list

    {Number List Range 2} = Column with Your parts list

    use 0 with the MATCH function to get an exact match(case sensitive) 1 and -1 can be used for an approximate match(results may vary)

    INDEX will return a value within a given range if you specify what row and what column it is in within that range. The column number is optional if you only have one column in your range.

    INDEX(range,row,column)

    if you substitute Match for the row portion. Match will return a value of the row it finds the match.

    MATCH(find, in this range of cells, Exact(0) or Approximate(1,-1))

    You can reference another sheet by clicking the link. and selecting the range of cells you want.

     

    Hope this helps.

    Sorry if it is confusing. I am not good at explaining things

     

  • I think I'm missing something at the beginning- I'm getting a circular reference error. Maybe I copied something verbatim that should have been filled with my own info?

    My source data sheet is called "Price Lookup PRODUCT." Column 1 is Item and column 2 is Price. I am selecting both of these columns for the lookup_table part of the formula.

    The destination data sheet is where the form would be sending information. Column 1 is Item, which is chosen from a dropdown menu in the form. This dropdown menu's options are verbatim what is in the Item column of the source sheet. 

    =VLOOKUP([email protected], {Price Lookup PRODUCT Range 2}, 2, false)

     

    Can you tell what I'm doing wrong? I appreciate the help!

  • THIS WORKED! Thank you so much. :)

  • Mike WildayMike Wilday ✭✭✭✭✭

    Hmmm. In theory, I don't see anything wrong. Does your Range 2 in your formula include both columns from the lookup page? 

  • I figured it out! And it works great. I used the INDEX function recommended below but apparently I can't use the same data set twice on the same sheet? When I update one, it updates the other. VLOOKUP it is. Thank you!

  • Mike WildayMike Wilday ✭✭✭✭✭

    Awesome. Glad I was able to help! 

  • edited 05/20/19

    Seems so simple...but.  I must be missing something.  Any guidance would be much appreciated.

    I'm trying to get a VLOOKUP to work to do just what this post discusses.  I'm similarly having trouble.

    My primary sheet has a column: "State" that is a dropdown list.  When State is populated from the dropdown, I want a column "Color" to auto-populate from another sheet called "Color Lookup".  Here's the formula I'm using:

    =VLOOKUP([State]@row, {Color Lookup}, 2, false)

    where "State" references the dropdown list in my primary sheet and "Color Lookup" is a simple list in the secondary sheet:

    State    Color

    WA       Blue

    OR       Red

    ID         Green

    Can anyone see what I'm missing please?  I'm getting a #INVALID REF error. I've confirmed the column names match (i.e. State and Color), the name of the lookup sheet is correct and "Color" is column 2 in the lookup table.

    Thank you!

    Kelly

  • Mike WildayMike Wilday ✭✭✭✭✭

    Hi Kelly, 

    The error you are getting is because its saying the reference doesn't exist. I would delete the reference to the other sheet and try recreating it. Make sure you select both the state and color column when you recreate the reference. 

  • Hi Mike ~ Awesome, that did it!  Thank you very much!

    Kelly

     

  • Mike WildayMike Wilday ✭✭✭✭✭

    Great! Glad I could be of service. 

  • I am also having an issue getting this to work.

    I am trying to get the value of a dropdown list to automatically display a text on another column.

    For some reason the first two rows work but then the rest just duplicate the value from the first row:

    This is the second sheet that i'm referencing:

    I am using the following formula:

    =VLOOKUP([Column2]@row, {New Sheet 2 Range 1}, 1)

    Can you please let me know where I am going wrong? any help will be appreciated.

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

    Hi @Bradley Casado,

    Try changing the 1 in the end to 0.

    Did it work?

    I hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • edited 01/22/20

    Changing the 1 to 0 gives me (#Incorrect Argument Set)

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

    Sorry about that!

    I misread the post.

    Try this.

    =VLOOKUP([Column2]@row, {New Sheet 2 Range 1}, 1,false)
    

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Andree, That works! but not for the purpose I intended. I am trying to select from the drop down list (column3) of numbers and have it automatically populate the text (column2) on a separate sheet. for example, If I drop down and choose 10270 then the cell next to it auto populates Access Flooring. Is there any way to do this?

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

    @Bradley Casado

    Yes, you'd need to change the range and the column to fetch and where it should look for a match.

    Let me know if you need any assistance!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • I am also having a hard time getting this to work. I have an employee list sheet and I would like that when a name is put in that it automatically fills out the phone number and email. I have a separate sheet with the colums User, Office, ext, Cell, and email. and idea on how I can make a dropdown menu with all the employee names auto fill the rest of the cells to match? Also is there a way to make a form sheet do that too?

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

    Hi @Brandon Der

    I'd be happy to take a quick look!

    Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

Sign In or Register to comment.