Share via

How can I auto fill cells based on selection from a Dropdown list?

Anonymous
2017-02-07T21:38:35+00:00

Hello, 

I would like to choose an item from a drop down list and have it auto populate cells below it. 

In this example, I would like to choose a Vendor for section 1 (in this case, Corporate Express) and have the contact, phone, fax, email and address populate based on the vendor selection.

![](https://learn-attachment.microsoft.com/api/attachments/b6dbc5ef-a100-41a4-a17f-1d0019e298ee?platform=QnA)

The information for the vendors are in a separate tab organized on on row like this:

Would love to know if this is possible and how to do it. 

Thank you!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. OssieMac 48,001 Reputation points Volunteer Moderator
    2017-02-08T03:42:34+00:00

    As simple as SmittyPro1 has indicated. Following is a bit more information for you

    See my formulas below for the following screen snippets.

    List of vendors in sheet2. Last character in each field is a numeral indicating which record they belong to. This assists when testing the output.

    Output on Sheet1

    Formulas used in cells as follows. Because you have "City or Province" and Country or Postal code" I assumed that you only want one or the other and in the table on Sheet2 one will be populated and the other blank so I have concatenated the 2 values.

    Assuming that your layout is the same as the screen snippets above then you can copy each formula and paste into your worksheet. (Just copy entire formula from the equals sign and ignore that the lines are broken to fit because they will paste without line feeds.)

    Cell B10:  =VLOOKUP(B8,Sheet2!$A:$A,1,FALSE)

    Cell B11:  =VLOOKUP($B$8,Sheet2!$A:$K,2,FALSE)

    Cell B12:  =VLOOKUP($B$8,Sheet2!$A:$K,4,FALSE)

    Cell B13:  =VLOOKUP($B$8,Sheet2!$A:$K,5,FALSE)

    Cell B14:  =VLOOKUP($B$8,Sheet2!$A:$K,6,FALSE)

    Cell B15:  =VLOOKUP($B$8,Sheet2!$A:$K,7,FALSE)

    Cell B16:  =VLOOKUP($B$8,Sheet2!$A:$K,8,FALSE) & VLOOKUP($B$8,Sheet2!$A:$K,9,FALSE)

    Cell B17:  =VLOOKUP($B$8,Sheet2!$A:$K,10,FALSE) & VLOOKUP($B$8,Sheet2!$A:$K,11,FALSE)

    20+ people found this answer helpful.
    0 comments
  2. Anonymous
    2017-02-10T01:21:36+00:00

    Thank you so much for this! It isn't quite working though... I think the issue is that I don't have two separate sheets - I just have different tabs in the same sheet. Is there a way to get around that?

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2017-02-08T15:51:59+00:00

    Excel 2010/2013/2016 Power Query (aka Get & Transform)

    No formulas, no VBA macro.

    Optimized for US usage.

    http://www.mediafire.com/file/1avn9dx001d6x2a/02_08_17.xlsx

    2 people found this answer helpful.
    0 comments
  4. OssieMac 48,001 Reputation points Volunteer Moderator
    2017-02-10T07:56:46+00:00

    Just curious why you think VLOOKUP wont work for all formulas?

    Example for B11 in your example:

    =VLOOKUP(B8,Sheet2!$A$2:$K$13,2,FALSE)

    To Murray,

    You have every right to be curious. Don't know what came over me but I took a relatively simple operation and turned it into a complex one.

    To Kayleigh Johnson,

    I have edited my previous post and corrected the formulas as per Murray's comment. If all is on the same worksheet then you can remove Sheet2! (note the included exclamation mark) from the formulas.

    My apologies to all for the error.

    1 person found this answer helpful.
    0 comments
  5. OssieMac 48,001 Reputation points Volunteer Moderator
    2017-02-10T03:42:07+00:00

    I think the issue is that I don't have two separate sheets - I just have different tabs in the same sheet. Is there a way to get around that?

    Not sure we are on the same wave length. I don't know how you can have different tabs in the same sheet. Perhaps an explanation of terminology is required.

    A Workbook is the file that you save.

    A worksheet is a tab within a workbook and there can be multiple worksheets in a workbook.

    Or maybe you mean that you have separate tables in the same worksheet. ie the list of vendors at the top of the worksheet and the table that you have that needs to be populated starts at row 8 below the vendor list on the same worksheet.

    Perhaps you can upload a copy of your workbook and I will have a look at it. If it contains any sensitive data then just replace the sensitive data with dummy data.

    Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

    1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
    3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
    4. Go to this link.  https://onedrive.live.com
    5. Use the same login Id and Password that you use for this forum.
    6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
    7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    8. Right click the file on OneDrive and select Share.
    9. Select "Get a Link" from the popup menu.
    10. Click Copy the link and Paste into your reply on this forum

    1 person found this answer helpful.
    0 comments