+ Reply to Thread
Results 1 to 3 of 3

Dynamic Data Validation

  1. #1
    Registered User
    Join Date
    02-01-2019
    Location
    Alberta
    MS-Off Ver
    2016
    Posts
    3

    Dynamic Data Validation

    excel help.png

    I am trying to figure out a formula for data validation.

    I want cell H9 to give me drop down options for anything that show in the 1st table at the top in the Disposal Types for that location.

    Thank you!!!

  2. #2
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Dynamic Data Validation

    you can find a lot of examples on the internet
    try:
    https://trumpexcel.com/dependent-dro...list-in-excel/
    https://www.excel-easy.com/examples/...own-lists.html

  3. #3
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,310

    Re: Dynamic Data Validation

    Here is a concrete example based on your picture. In the attached workbook I have used the same row/columns as you provided in your picture. I have populated only the subset of cells needed to demonstrate the solution. As follows:

    DisposalTypeDropdown.png

    I used a "helper" range in K2:K6. For simplicity this should be in the same set of rows as your first table. In K2 copied down to K6:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This simply takes the "Disposal Type" value if "Location" is WestTest or blank otherwise.
    B16:B19 are the values to be used in your dropdown. In B16 and then copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This is an array formula and must be committed with CTRL-SHIFT-ENTER. It takes the helper column values, removes duplicates and and shuffles values upwards to eliminate any blank rows in the helper column. Copy the B16 formula down for as many different "Disposal Types" as you have. Note - this group of cells can be relocated wherever you like.

    Create a named range WestTestDropdown defined as follows:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This will be used to define your H9 dropdown list values. Its sole purpose is to eliminate blank entries at the end of the dropdown list that would occur if the B16:B19 range was used directly.

    Finally in the Data Validation List, for "Source" type =WestTestDropdown

    The attached workbook implements the above. Hopefully there is enough info here to allow you to implement something similar in your real workbook.

    Let me know how it goes.
    Attached Files Attached Files
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

+ 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. Dynamic Data Validation
    By Phonix in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-30-2017, 02:57 AM
  2. [SOLVED] Dynamic Data Validation
    By Perk1961 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 10-26-2015, 01:56 PM
  3. If statement based data validation? Dynamic data validation?
    By bjohnsonac in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-02-2014, 02:12 PM
  4. [SOLVED] Dynamic data validation
    By phalcon45 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-07-2014, 02:33 AM
  5. Dynamic Data Validation
    By Badvgood in forum Excel General
    Replies: 3
    Last Post: 12-19-2011, 07:44 PM
  6. Dynamic Data Validation
    By SystemsAccountant in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-24-2009, 12:01 PM
  7. Dynamic Data Validation
    By Alex in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-15-2005, 07:05 AM

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