+ Reply to Thread
Results 1 to 8 of 8

Dynamic Cascading Drop Downs (getting around the INDIRECT() problem)

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

    Dynamic Cascading Drop Downs (getting around the INDIRECT() problem)

    Cascading Dependent Drop Down boxes are a fun and common trick, allowing a second drop down list to change its options based on the selection made in a first drop down box. This is commonly accomplished using the INDIRECT() function.

    Another common trick is to use Dynamic Named Range formulas to create named ranges that adjust themselves as you add items to the column. Very useful.

    PROBLEM: These two "tricks" do not work together. If you use Dynamic Named Range formulas to create a list of Teams and then use that named range as the source of the DV list in cell A1, you cannot use the INDIRECT(A1) method to select the dependent named range that has the same name as the selected text in A1.

    SOLUTION: The workaround then is to not create dynamic named range formulas at all. Instead, you move all the dynamic activity into the Dependent Data Validation "Source" formula.

    SETUP:

    1.On a Rosters sheet all your lists will reside side-by-side in columns, set them up like so:



    2. We create a named range called AnchorCell by clicking on A1 and typing that name into the name box as shown above.
    This allows us to create a data validation formula later that will still work on Excel 2003.

    3.We create a dynamic named range called Teams by pressing CTRL-F3 and defining the name with the RefersTo formula of:
    =OFFSET(AnchorCell, , , 1, COUNTA(OFFSET(AnchorCell,,,,100)))



    This allows you to add new columns (Teams) anytime you want without having to change anything else, it will all keep working and include your new teams as well.
    NOTE: No blank columns, this is a reference sheet, keep it tidy.


    4. Next, nothing fancy here, we use the named range Teams as the list source for our column A primary data validation on the Selections sheet:



    Once applied, it provides a list of the teams from row1 of our Rosters sheet:



    5. And here's the magic. The Data Validation list formula in B2 does all the heavy lifting, using OFFSET() and MATCH functions to find the team chosen in column A on row1 of the Rosters sheet, then create a drop down of only the items in that column. In B2, the DV formula would be:

    =OFFSET(AnchorCell, 1, MATCH($A2, Teams, 0)-1, COUNTA(OFFSET(AnchorCell, , MATCH($A2, Teams, 0)-1, 50, 1))-1, 1)

    You should spend some time reading the help files on offset so the parameters make sense to you:
    =OFFSET(reference, rows, columns, [height], [width])


    6. Once applied, the secondary list creates itself based on the choice made in the column A cell:



    A sample file is attached to demonstrate. DependentDYNAMICLists.xls
    Last edited by JBeaucaire; 02-12-2013 at 03:26 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!)

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Dynamic Cascading Drop Downs (getting around the INDIRECT() problem)

    Awesome, a very good learning for me.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Dynamic Cascading Drop Downs (getting around the INDIRECT() problem)

    I think it's easier to use another defined name using EVALUATE instead of INDIRECT, since that will work with a DNR.
    Remember what the dormouse said
    Feed your head

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Dynamic Cascading Drop Downs (getting around the INDIRECT() problem)

    What is DNR?

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Dynamic Cascading Drop Downs (getting around the INDIRECT() problem)

    Dynamic Named Range.

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Dynamic Cascading Drop Downs (getting around the INDIRECT() problem)

    Oops yeah...i should have guessed

  7. #7
    Registered User
    Join Date
    11-30-2012
    Location
    London, england
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Dynamic Cascading Drop Downs (getting around the INDIRECT() problem)

    Thanks a lot! Exactly what I was looking for

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

    Re: Dynamic Cascading Drop Downs (getting around the INDIRECT() problem)

    To maintain full Excel 2003 full compatibility, I've updated this thread and changed step #3 to properly use the AnchorCell in the formula so there is no direct reference to another sheet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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