+ Reply to Thread
Results 1 to 8 of 8

Auto-populate field based on drop down box selection

  1. #1
    Registered User
    Join Date
    01-20-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    6

    Auto-populate field based on drop down box selection

    I'm hoping someone can help with an issue I have with a form.

    I have a dropdown list selection on the form for types of machines. The list of machines, their sizes and related noise level are in a chart on worksheet 2 and I'd like to automate the form so that when the machine is selected, the related noise level auto populates.

    Can anyone shed light on how to do this?

    The dropdown box has been put in the form using the data validation function.

  2. #2
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Auto-populate field based on drop down box selection

    nicfarrell, welcome to the forum. Can you upload a small sample file, showing the type and arrangement of your data? What you want to do can be easily accomplished; however members can be disinclined to make up a data-set, as a precursor to assisting you.
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.

  3. #3
    Registered User
    Join Date
    01-20-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Auto-populate field based on drop down box selection

    Hi Brendan,

    Thanks for the welcome and for the advice. I've uploaded an example of the data and the form that I'm working with.

    Nicola

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Auto-populate field based on drop down box selection

    Hi nicfarrell and welcome to the forum

    before I suggest a solution for you, I will offer some advice. PLZ try to avoid using "merge and center", it will only cause you problems later on when you start using formulas to reference other cells

    OK, having said that, in F3, use this...
    =VLOOKUP(B3,Sheet2!$A$3:$E$25,4,FALSE)
    and in H3, use this...
    =VLOOKUP(B3,Sheet2!$A$3:$E$25,3,FALSE)

    Note: in the file you uploaded there was no DV/drop-down
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    01-20-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Auto-populate field based on drop down box selection

    Fab FDibbins!! Thanks so much. I've been wading through help articles to work out how to do it and you've done it for me in a few minutes. That's awesome.

    If you have a moment can you explain a little how it works?

    And I'll take the note on auto merge - I always do it and, also, always get issues when using formulas... I'll learn

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Auto-populate field based on drop down box selection

    Happy to help

    I used a standard "off-the-shelf" vlookup() function, which works like this...

    =VLOOKUP(what-you-want-to-find,range-to-search-in,column-that-contains-the-match,FALSE) FALSE is for finding an exact match

    So what it does, is take whatever you have in B3, looks down the FIRST column of the search range, and when it finds a match, returns whatever is in the (in this case) 4th column - or 3rd column for the 2nd answer (you have no entry in the L90 column for jackhammer, so thats what you get back - nothing)

    Not sure if you had a Data Validation.Drop-Down in your original file, but if not, High-light all the data in column A on sheet2 (A3:A25), right-click on it and select "name a range" and give a name (whatever you want, I called it MACHINES)

    Then on sheet1 B3, select DATA tab/data validation, select ALLOW/LIST in SOURCE, enter =machines. Now, you have a drop-down in B3. Note however, that you have the data in L90 amd MAX noice fornated as left aligned - the D/D arrow will hide the answer in L90 unless you right-align the data

    If you need to have things look as if they were merged, high-light the range you would have applied merge to, right-click, select format, select alignment/horizontal/center across selection

    Hope this helps

  7. #7
    Registered User
    Join Date
    01-20-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Auto-populate field based on drop down box selection

    Thanks so much FDibbins - that's a huge help

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Auto-populate field based on drop down box selection

    Happy to help, and thanks for the rep

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1