+ Reply to Thread
Results 1 to 2 of 2

Two drop down lists linked

  1. #1
    Registered User
    Join Date
    07-13-2017
    Location
    Ireland
    MS-Off Ver
    Office 2010
    Posts
    1

    Two drop down lists linked

    have two lists that I want to link. See the image here http://tinypic.com/view.php?pic=5y4z9f&s=9#.WWeZixXyuM9
    I want to populate one drop down list (in a cell) with values from the column on the left (Access Control and Building Fabric). I want a second drop down list in a cell populated with values in the column on the right. When Access Control is selected from the first drop down the contents of the second drop down must change to the contents of what is directly across from it in the pic.
    When Building Fabric is selected I want the drop down to change and those corresponding values to appear in it.
    Can anyone help me to do this? I have to use VLookup, I know that much. I haven't been able to find a similar example online.

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,834

    Re: Two drop down lists linked

    Ok do first thing to fill repeat all the first column value into each row. Follow the below steps

    Select the column then press Control->G you will get a window called Go to special now click on special tab and check mark on blank cell then ok. This step will select all the blank cells now press = and arrow up key the Control+Enter press. All the previous value will be automatically updated in blank cells.

    Assume you have Order ID in column A then for getting unique ID drop down create a name manger press Alt->M->N->New->Give the name order-> In refer to section put =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$999))

    Assume you have Product in column B do the same as we did in first step for getting unique ID and just change refer to section to =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$999)) and name with product.

    These above thing is for expendable ranges which me whenever you will increase your database these formulas will increase or decrease ranges according your data input.

    Now for drop down....

    We have expandable ranges for Order ID and Product so for getting unique dropdown put below formulas into Sheet 2

    A2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    B2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Both above formulas are array formula.

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Create a name manger again for drop down.

    Name manger name will be Uniqueorder and refer to value would be =OFFSET(Sheet2!$A$2, 0, 0, COUNT(IF(Sheet2!$A$2:$A$1000="", "", 1)), 1)

    Next Name manger will be Uniqueproduct and refer to value would be =OFFSET(Sheet2!$B$2, 0, 0, COUNT(IF(Sheet2!$B$2:$B$1000="", "", 1)), 1).

    Now in sheet1 D2

    Press Alt-A-V-V you will get a window data validation in allow section select List then in source section press F3 you will have to select first dropdown which would be uniqueorder then ok.

    Do the same above thing for product just selection part only change which will be Uniqueproduct.

    Check the attached file for better understanding.
    Attached Files Attached Files
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Create two linked drop-down lists
    By jnmc13 in forum Excel General
    Replies: 6
    Last Post: 11-11-2015, 10:24 AM
  2. Replies: 0
    Last Post: 11-03-2014, 12:14 AM
  3. Linked Drop down lists and vlookup?
    By mveda2004 in forum Excel General
    Replies: 3
    Last Post: 02-10-2012, 09:04 AM
  4. Multiple linked drop down lists (Master drop down)
    By zaalibhai in forum Excel General
    Replies: 1
    Last Post: 06-23-2011, 10:51 AM
  5. drop down lists referencing tables creating other drop down lists!!
    By Stumped- in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 08-07-2009, 11:29 AM
  6. Linked drop-down lists
    By jerryliang2k in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-23-2008, 07:20 PM
  7. linked drop down lists
    By Priglett in forum Excel General
    Replies: 2
    Last Post: 09-09-2005, 02:05 PM

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