Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: How can I create a Drop-Down List to populate other cells?

  1. #1
    New Member
    Join Date
    Oct 2009
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How can I create a Drop-Down List to populate other cells?

    Ok, guys, new to this forum and I need some help with a business invoice. I know how to create a drop down list. But what I want to do is create a Drop-Down list with customers names, say cell A11. After selecting a customer name from the list, It would be able to populate the address in cells A12 and A13. Is this possible?

  2. #2
    Board Regular crimson_b1ade's Avatar
    Join Date
    Sep 2008
    Location
    Yonkers, New York
    Posts
    1,557
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I create a Drop-Down List to populate other cells?

    use VLOOKUP.
    You may not know the secrets of the Universe but there is one thing you should know. Everyone born in and of this Earth knows not more to such matters than you and possesses no more clairvoyance or holiness than you. So if you ought to believe in something…believe in YOURSELF!

  3. #3
    New Member
    Join Date
    Oct 2009
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I create a Drop-Down List to populate other cells?

    Quote Originally Posted by crimson_b1ade View Post
    use VLOOKUP.
    Thanks for the help.
    I looked up VLOOKUP. It looks really complicated and I am not sure where to even start.

    If on Sheet 2, for instance, I have A9 (Customer Name), A10 (Customer Address), and A11 (Customer Address Cont.), and so one for hundreds of customers including a space cell between (ex. A12).

    First, how will I be able to create a drop down list in Sheet 1, Cell A11, with just Customer Names (A9, A13, A17, ....)?

    Second, if I select A9, how do I get Sheet 2, A10 and A11, to populate in Sheet 1, A12 and A13?

  4. #4
    Board Regular
    Join Date
    Sep 2009
    Location
    Sussex, UK
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I create a Drop-Down List to populate other cells?

    Quote Originally Posted by wako182774 View Post
    First, how will I be able to create a drop down list in Sheet 1, Cell A11, with just Customer Names (A9, A13, A17, ....)?

    Second, if I select A9, how do I get Sheet 2, A10 and A11, to populate in Sheet 1, A12 and A13?
    You would need a list of all the names in a column, then use data validation to create the dropdown. Then use vlookup the find the address etc according to the name selected

  5. #5
    Board Regular crimson_b1ade's Avatar
    Join Date
    Sep 2008
    Location
    Yonkers, New York
    Posts
    1,557
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I create a Drop-Down List to populate other cells?

    Quote Originally Posted by wako182774 View Post

    ...If on Sheet 2, for instance, I have A9 (Customer Name), A10 (Customer Address), and A11 (Customer Address Cont.), and so one for hundreds of customers including a space cell between (ex. A12)...

    Firstly, your data is not in the appropriate format (e.g. List Format) to create a drop down. You'll want to have your names, and addresses going down separate columns (not the same column). In addition, you shouldn't have blank rows in your list. Example: Names in Column A, Address in Column B.

    Second, to create cell drop downs on Sheet1 (for example...and lets say your Name is in Column A on Sheet2), click Data then Validation on the menu bar. In the Data Validation popup ("DV"), change the "Allow" criteria to "List" then in the source ref box put in =Sheet2!$A$1:$A$10 (this assumes you have ten rows of data).

    Now to look up the address based on the value selected from the dropdown you'll use vlookup. Let's assume the dropdown was in cell A1 on Sheet1 and you want to populate the address in Sheet1 in cell B1. So to achieve this, in Cell B1 of Sheet1 you'll put the following function: =VLOOKUP(A1,Sheet2!$A$1:$B$10,2,False)

    The above function simply means..Lookup cell A1 name value, in Sheet2 column A, and return the 2nd column of the range (column B in this case), and the last part of the function we'll put "False" because we want an exact name match.
    Last edited by crimson_b1ade; Oct 18th, 2009 at 07:41 PM.
    You may not know the secrets of the Universe but there is one thing you should know. Everyone born in and of this Earth knows not more to such matters than you and possesses no more clairvoyance or holiness than you. So if you ought to believe in something…believe in YOURSELF!

  6. #6
    New Member
    Join Date
    Oct 2009
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I create a Drop-Down List to populate other cells?

    Quote Originally Posted by crimson_b1ade View Post
    Second, to create cell drop downs on Sheet1 (for example...and lets say your Name is in Column A on Sheet2), click Data then Validation on the menu bar. In the Data Validation popup ("DV"), change the "Allow" criteria to "List" then in the source ref box put in =Sheet2!$A$1:$A$10 (this assumes you have ten rows of data).

    Now to look up the address based on the value selected from the dropdown you'll use vlookup. Let's assume the dropdown was in cell A1 on Sheet1 and you want to populate the address in Sheet1 in cell B1. So to achieve this, in Cell B1 of Sheet1 you'll put the following function: =VLOOKUP(A1,Sheet2!$A$1:$B$10,2,False)

    The above function simply means..Lookup cell A1 name value, in Sheet2 column A, and return the 2nd column of the range (column B in this case), and the last part of the function we'll put "False" because we want an exact name match.
    Ok, and thanks for you help again, but I am still having a little problem.
    On Sheet2, A13:A17 has Customer Names, B13:B17 has Address 1, and C13:C17 has Address 2 (The contents are dummy informations that I am using for Test). Also, I used Define Name for cells A13:A17 as Customer_Name.

    In Sheet1, I have A11 as the Drop-Box using Allow: List, Ignore Blank, In-cell dropdown, and Source: =Customer_Name. The Drop Down List works fine, showing customer names only.

    Here is the problem. For cell A12, I would like Address 1 returned based on the Customer Name selected. This is the string entered: =VLOOKUP(A13,Sheet2!$A$13:$C$17,2,FALSE). I am getting a #N/A return.

    Two things to note, not sure if they have any effect.
    1. I am using Excel 2007.
    2. On Sheet2, A12 reads "Customer Name," B12 reads "Address 1," and C12 reads "Address 2.)

    Thanks for any help again.

  7. #7
    New Member
    Join Date
    Oct 2009
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I create a Drop-Down List to populate other cells?

    Never mind, thanks again. I got it to work using the Formula function within Excel.

  8. #8
    Board Regular Purple Youko's Avatar
    Join Date
    Sep 2004
    Posts
    265
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I create a Drop-Down List to populate other cells?

    Here is the problem. For cell A12, I would like Address 1 returned based on the Customer Name selected. This is the string entered: =VLOOKUP(A13,Sheet2!$A$13:$C$17,2,FALSE). I am getting a #N/A return.
    The first id in a vlookup is the cell in which the reference value is found.
    In your case the list which is in cell A11 so your formula should be
    =VLOOKUP(A11,Sheet2!$A$13:$C$17,2,FALSE)

  9. #9
    New Member
    Join Date
    Oct 2009
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I create a Drop-Down List to populate other cells?

    Quote Originally Posted by Purple Youko View Post
    The first id in a vlookup is the cell in which the reference value is found.
    In your case the list which is in cell A11 so your formula should be
    =VLOOKUP(A11,Sheet2!$A$13:$C$17,2,FALSE)
    Your right, That is what I had wrong. Thanks for the help, will definetly have more questions in the future.

  10. #10
    New Member
    Join Date
    Dec 2010
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I create a Drop-Down List to populate other cells?

    Hmm, ok so this might help me make an inventory spreadsheet for my bar, but how would I get it to auto-populate several cells instead of one. For example, if I did a drop down list to select a brand of liquor. Then I want it to auto-populate Category (type of liquor), bottle size (ounces), bottle size (metric) and tare weight (ounces). I have two pages Liquor and Tare Weights, with the data on Tare Weighs. The drop down list would be in say A2 and category would be B2, bottle size (ounces) C2, bottle size (metric) D2 and tare weight on E2 all on the Liquor page. I tried the above formula, but it only worked for category and I can't seem to get the others working. Thanks in advance.
    Last edited by gleemonex69; Aug 27th, 2013 at 05:01 PM. Reason: Missing data

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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