+ Reply to Thread
Results 1 to 6 of 6

HELP: Dynamic Data Validation or Drop Down

  1. #1
    Registered User
    Join Date
    06-29-2015
    Location
    Manila, Philippines
    MS-Off Ver
    2013
    Posts
    12

    HELP: Dynamic Data Validation or Drop Down

    I wanted to create a dynamic drop down/data validation that is dependent on the previous selection, I saw some samples online but those examples forces me to name the column headers using the values on the first column. It seems impossible for me to explain the functionality I need without images so I will attach the images first. This is just a sample table

    This is my first Table:
    Table1.JPG

    I have Column Name and Contact #, as you can see it is possible that there are names with multiple entries(contact numbers)

    Here's my second table where I want to put my drop downs
    Table 2.JPG

    I just want the drop down under customers reference to Names Column from the previous table but with unique distinct values
    drop down for contact numbers for all contact numbers registered with the name I select from the previous drop down.
    hope you gusy can help me I am new here and a noob in excel an I want to learn
    Attached Images Attached Images

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: HELP: Dynamic Data Validation or Drop Down

    1) On the reference sheet (sheet1), setup your contact table like this:
    Data Range
    A
    B
    C
    D
    E
    1
    Bryan Shaw
    Angela Stevens
    Jon Snow
    Tom Cruise
    2
    12345
    34344
    454533
    43234
    3
    122345
    56133
    435435
    324115
    4
    54143
    51566
    5
    16143446
    6

    2) Now create a single NAMED RANGE which will serve to list your names:
    Name = MyNames
    Refers To: =OFFSET(Sheet1!$A$1, , , , COUNTA(Sheet1!$1:$1))

    Notice this is dynamic, it will encompass all the names listed in row1.

    3) Back on your main sheet, in cell B2 apply DV settings to use:
    Allow: List
    Source: =MyNames

    4) IMPORTANT... select a name in B2 for now, any name is fine. Then click on C2. You must have C2 highlighted for this next step to work.

    5) With C2 highlighted, apply this new dynamic DV formula:
    Allow: List
    Source: =OFFSET(MyNames, 1, MATCH(B2, MyNames, 0)-1, COUNTA(OFFSET(MyNames, 1, MATCH(B2, MyNames, 0)-1, 1000, 1)),1)


    All done. Now copy B2 and C2 down as far as needed, then clear the values and they're ready to use.
    Attached Files Attached Files
    Last edited by JBeaucaire; 06-29-2015 at 11:12 AM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    06-29-2015
    Location
    Manila, Philippines
    MS-Off Ver
    2013
    Posts
    12

    Re: HELP: Dynamic Data Validation or Drop Down

    Hi JBeaucaire,

    Thank you for replying, however I need the names to be on a single column because in the actual implementation the names and the numbers are considered data entries so I need them to be in a Row

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: HELP: Dynamic Data Validation or Drop Down

    The you will need to extract the names into another region to serve as the unique data for the first drop down. You can use DATA > ADVANCED FILTER to make a copy of unique values to that secondary location. Then an OFFSET() function can then be used to create the second drop down dynamically using your original table above.

    If you want to post an actual workbook that exactly represents your really data layout (don't change anything that would affect the layout just desensitize the data) then I can offer something exact.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  5. #5
    Registered User
    Join Date
    06-29-2015
    Location
    Manila, Philippines
    MS-Off Ver
    2013
    Posts
    12

    Re: HELP: Dynamic Data Validation or Drop Down

    Thank you so much for taking your time answering my inquiry. I have a attached my actual sample work book, this is a workbook that I am trying to create as an alternative database/tracker for issues that am trying to resolve.

    There are 3 sheets, Customer, Accounts, Issues
    The Customer Tab contains all of the customer's names, at any point in time I need to add customer names in the list

    The Account Tab contains all of the customer's numbers. There is a possibility that each customer may have multiple numbers
    Issue in Accounts Tab:
    I created a drop down under the customer's name using Data Validation but the list is not dynamic, when I add another entry to customer's list the new entry is not added in the drop down and I don't know how to reference the data validation source from a Table Column instead of a Cell Coordinates

    The Issue Tab: contains all of the customer complaints, issues similar with accounts the relationship is one to many
    Similar issue with the customer name drop down in accounts tab
    Another issue: I do not know how to create a drop down in the mobile number column where it will only show all of the numbers under the name of the customer selected on the customer's column.

    apologies if my explanation would be hard to understand.
    Attached Files Attached Files

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: HELP: Dynamic Data Validation or Drop Down

    My best suggestion I've already given you and it's been set aside. Nothing about this new workbook explains fully to me why it can't be used, but ok.

    Your original example for your layout would work, this new example you created (which may be more accurate based on the reason you gave for setting my solution aside) won't work for any of the dynamic methods I employ. Like the original sample the data would need to be sorted so all like items are always together.

+ 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. [SOLVED] need a dynamic source for data validation drop down lists
    By dredwolf in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-29-2012, 11:01 PM
  2. [SOLVED] Dynamic Data validation Drop down
    By SPACKlick in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-18-2012, 09:30 AM
  3. Excel 2007 : Drop down validation AND dynamic list
    By teddy.montoya in forum Excel General
    Replies: 11
    Last Post: 12-14-2010, 12:14 PM
  4. Reversing a Dynamic Drop-Down List in Data Validation..
    By e4excel in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-18-2008, 02:09 AM
  5. [SOLVED] Data validation drop downs don't recognize dynamic named range
    By GlenC in forum Excel General
    Replies: 0
    Last Post: 07-19-2006, 01:30 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