+ Reply to Thread
Results 1 to 11 of 11

Trying to create a dynamic dependant dropdown based on two-dimensional data

  1. #1
    Registered User
    Join Date
    06-26-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2007
    Posts
    4

    Trying to create a dynamic dependant dropdown based on two-dimensional data

    example.xlsx

    I have a workbook with the first page containing strings of 2 and 3 character letter groups. Attached is the example of the data page. What i need is the ability to sort through all the cells and generate a list of cell addresses based on pre-determined search criteria. Such as searching for the letters "VC". Some of the lists i need may be dependant on finding a cell meeting several criteria such as "OGR" AND "CI".

    The resultant list of cell addresses would be most useful in a named range so i can put them in a dropdown menu on different worksheets. I just can't figure out how to create a named range that will return what i need across the entire range of cells.

    This worksheet cannot grow larger then A1:Z25, but all cells within that range are fair game and i will be changing them over time to track what i need.

    Any ideas, pointers?

    Any help would be greatly appreciated as i am REALLY lost.

  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: Trying to create a dynamic dependant dropdown based on two-dimensional data

    I guess VBA would have to be employed not to create a named range, that wouldn't work with your layout, but to construct a hardcoded string. Or it could collect all the matching values into a specific column on that same sheet and give you a named range of that column you could use. That could even be dynamic.

    So, to know HOW best to do this, we need to see exactly how you're going to be trying to use it on the other sheet(s). Update your sample workbook to include a mockup of how you're wanting it to be used, mock up a drop down list manually if needed to show the results you'd like to see automated.
    _________________
    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
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Trying to create a dynamic dependant dropdown based on two-dimensional data

    Agreed with JBeaucaire that a VBA solution might be your best, though you could also construct a formula-based solution as in the attached (drop-down in column AE). Kind of depends, as JBeaucaire pointed out, what exactly you're wanting to do.

    Regards
    Attached Files Attached Files
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Registered User
    Join Date
    06-26-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Trying to create a dynamic dependant dropdown based on two-dimensional data

    example2.xlsx

    This is the example. In this case i need a list of cells that contain VC, MI and list the cell addresses in a dropdown list. A dependant cell next to it would display the full contents of the cell next to it(simple, just an indirect function). I would need to do this 5 or 6 times on each sheet, with 13 sheets in total, each list would be searching for a different combination, but the combinations would all be preset.

    Examples of what i would be searching for would be:

    VC

    VC, CI

    OGR
    OGR, MI

    VM
    VM, WO

    Everything i intend to do with it after that is simple addition and subtraction. It's generating the list that's killing me.

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Trying to create a dynamic dependant dropdown based on two-dimensional data

    Did you look at my attachment?

  6. #6
    Registered User
    Join Date
    06-26-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Trying to create a dynamic dependant dropdown based on two-dimensional data

    Yes I did
    That solution involves a function i havent seen before, but i believe it would work. In order to make it function i would need to simply generate 12 lists based on the first letter string, meaning a list of ALL the vc cells, or OGR, or WE.
    One question, as i was messing with your attachment, when i changed cell AA1 to "OGR", or "VC" or something other than "WE", a few of the cells in column AD displayed "0"?

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Trying to create a dynamic dependant dropdown based on two-dimensional data

    Yes, of course. Sorry. Had to make a few amendments. Please test in the attached.

    Regards
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-26-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Trying to create a dynamic dependant dropdown based on two-dimensional data

    That is friggin epic, thanks alot bro that really helps me out

    Take care

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

    Re: Trying to create a dynamic dependant dropdown based on two-dimensional data

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Trying to create a dynamic dependant dropdown based on two-dimensional data

    No worries. Good luck with the rest.

  11. #11
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Trying to create a dynamic dependant dropdown based on two-dimensional data

    Wait! Noticed a small error. Input, e.g. "PT" and you'll see there is no return for "19.2".

    Fixed in this version.

    Regards
    Attached Files Attached Files

+ 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