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

Dajah
Dajah
edited 12/09/19 in Formulas and Functions

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.

«1

Comments

  • Mike Wilday
    Mike 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). 

  • cslatten
    cslatten
    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(Item@row, {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 Wilday
    Mike 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 Wilday
    Mike Wilday ✭✭✭✭✭✭

    Awesome. Glad I was able to help! 

  • Kelly M
    Kelly M
    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 Wilday
    Mike 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 Wilday
    Mike 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

    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.

  • Bradley Casado
    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)
    

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!