+ Reply to Thread
Results 1 to 9 of 9

Dynamic Validation Lists using Formula. Dependent and Independent

  1. #1
    Registered User
    Join Date
    07-25-2012
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2003
    Posts
    23

    Dynamic Validation Lists using Formula. Dependent and Independent

    Hello-

    I'm no expert and have been racking my brain but can't seem to figure out my validation list issue. I'm trying to conceptualize how I can have dependent and independent validation lists. I'm trying to create a workbook where one validation is a subset of the previous with the third validation being a subset of the subset (Market Segment, Product Family, CBook 4.xlsxustomer). I cannot use name manager because some of the values are shared where, for example, depending on the segment, the same product family will have different customers. Additionally, the name manager option forces one or both of the previous data validation lists to be populated. Essentially the goal is to allow a user to filter report results with similar functionality of a pivot table, where the combination of the 3 validation lists will yield unique results. I've drawn up the general idea as I believe I'll need some helper cells to make this work, but just cannot see the big picture as to how to make 3 dynamic validation lists through a formula. Ultimately I need each validation list to reference a range whose formula's results are dependent on the selection in the other two validation lists. Perhaps there is a simpler solution and I've just stared at this too long to see it and I'm also open to any other paths that would get me closer, but I cannot seem to make progress with this issue I'm faced with.

    Any help would be appreciated as I've searched for a solution without success.

    Thanks in advance.

    Joe

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,743

    Re: Dynamic Validation Lists using Formula. Dependent and Independent

    i will take a look at ypour file and see what i can come up with. in the mean time, play around with indirect() and range names.

    i have done something similar for another member, and used DV and list for the 1st drop-down. then used indirect() based off the 1st D/D to find specific values to populate the 2nd D/D. the same could be done for the 3rd.

    but everything revolves around having distinct name ranges for each catory of data

    edit:
    took at look at your data. based on the 1st criteria
    Total
    Market Segment 1
    Market Segment 2
    Market Segment 3

    which product family would be included in Market Segment 1, what in 2 and what in 3?
    likewise, if market 1 and PF1 were selected, what customer names would you expect to see?

    from your description above, it sounds like you may have to make up multiple ranges with perhaps duplicate info in them
    Last edited by FDibbins; 09-26-2012 at 08:40 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    07-25-2012
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Dynamic Validation Lists using Formula. Dependent and Independent

    The named range is my main issue as, for example, Product Family 1 consists of a different set of Customers based on the Segment selected. I initially pursued this path but found that I'd have to rename the standard jargon folks are used to in order to make it work. Additionally, although I created the general idea of my issue in the attachment, I have 30+ Product Familes and 50+ Customers and fear recreating named ranges for this potentially recurring file would be less optimal than a lookup solution.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,743

    Re: Dynamic Validation Lists using Formula. Dependent and Independent

    ok so, as stated above, how do we know

    Please Login or Register  to view this content.
    if you cant sort that out, we will have a hard time figuring out how to help you

  5. #5
    Registered User
    Join Date
    07-25-2012
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Dynamic Validation Lists using Formula. Dependent and Independent

    I apologize if the original attachment wasn't clear as I'd shortened Product Family to PF1, PF2, etc. I'd put together the example to illustrate that some Product Families aren't in some Segments, similar to Customers which aren't in some Product Families.
    Attached Files Attached Files

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,743

    Re: Dynamic Validation Lists using Formula. Dependent and Independent

    i have created the drop-downs for you, using custom DV and a vlookup inside another vlookup (see attached)

    however, how you will identify which Prod Fam's go with which Segments, and which Customers go with which Prod Fam's is still something you will need to figure out
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-25-2012
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Dynamic Validation Lists using Formula. Dependent and Independent

    FDibbins - I appreciate your help but I fear I may have to plead ignorance as to the changes you've made with respect to the data validation. Could you please explain? And of course it wasn't until I saw your message again did I realize what you were referring to about identifying which PF go with which Segments and so forth. For what it's worth, this is the path that I'd gone down to separate the data so I'm thrilled I got that right, but I don't seem to understand/notice the custom data validation you refer to on your attachment. Could you help clarify?

    Thanks,

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,743

    Re: Dynamic Validation Lists using Formula. Dependent and Independent

    1st off, is the DV working for you?

    im on 2003 at the monet too (was on 2007 before). when you go into DV, from the "allow" drop-down, select "custom", and then enter/copy my formula into the "formula" box

    eg =VLOOKUP(VLOOKUP(N19,$N$4:$O$7,2,FALSE),$P$4:$Q$10,2)

  9. #9
    Registered User
    Join Date
    07-25-2012
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Dynamic Validation Lists using Formula. Dependent and Independent

    Unfortunately in 2010 I get a "this type of reference cannot be used in a data validation formula" error when selecting "custom". The error also states "Change the reference to a single cell, or use the reference with a worksheet function, such as SUM(A1:E5)" This prompted me to think the workaround could be a lookup outside of the DV source field that the DV source could reference. I began working on a solution thinking that each Segment, Product Family and Customer could have an area for the lookup formula that the DV source would reference. I set up the matrices of each Seg/PF/Customer combination but once again I can't seem to conceptualize the logic of the formula as each validation list is contingent on a combination of the other two validations. I took a portion of the the lookup matrices on the attached and created a section for the validation source highlighted in yellow. I either cluttered this up or got closer. I suppose changing the lookup formula in each of the highlighted category sections to include the second reference to the other category would solve my issue. I'm baffled as this is the point at which I'd gotten on my real file that prompted me to post my thread originally. Any further help or hints would truly be appreciated.
    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)

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