+ Reply to Thread
Results 1 to 18 of 18

Create 7 dependent dropdown using data validation based on multiple dependent columns

  1. #1
    Registered User
    Join Date
    09-03-2020
    Location
    INDIA
    MS-Off Ver
    2013
    Posts
    18

    Create 7 dependent dropdown using data validation based on multiple dependent columns

    HI


    I want to create 7 dependent / cascading dropdown lists. Based on those 7 filters (drop down selection) i want to create reports. Please find the sample source.

    (iam sorry, I couldn't do sample output)


    Regards
    Raj Kumar

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Create 7 dependent dropdown using data validation based on multiple dependent columns

    first place your data in priority order that you want your data filtering.
    i.e. top priority in column 'A' etc.,
    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.

  3. #3
    Registered User
    Join Date
    09-03-2020
    Location
    INDIA
    MS-Off Ver
    2013
    Posts
    18

    Re: Create 7 dependent dropdown using data validation based on multiple dependent columns

    HI



    I adjusted the data on priority wise. dependent dropdown should create by using "excel formulas" only. (No slicer/VBA)


    Thank you in advance


    regards
    Raj Kumar

  4. #4
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Create 7 dependent dropdown using data validation based on multiple dependent columns

    Sorry my solution would be with VBA.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Create 7 dependent dropdown using data validation based on multiple dependent columns

    Removed by JT
    Last edited by JohnTopley; 11-06-2021 at 07:38 AM.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  6. #6
    Forum Contributor
    Join Date
    04-15-2013
    Location
    Singapore, Melbourne
    MS-Off Ver
    Office 365
    Posts
    167

    Re: Create 7 dependent dropdown using data validation based on multiple dependent columns

    Why not use Slicer? A 7 level Dropdown list doesn't look user friendly.
    Row row row your boat
    Gently down the stream

  7. #7
    Registered User
    Join Date
    09-03-2020
    Location
    INDIA
    MS-Off Ver
    2013
    Posts
    18

    Re: Create 7 dependent dropdown using data validation based on multiple dependent columns

    hi torachan, If there is no solution with forumula , Please share vba solution. I would be greateful to you.

  8. #8
    Registered User
    Join Date
    09-03-2020
    Location
    INDIA
    MS-Off Ver
    2013
    Posts
    18

    Re: Create 7 dependent dropdown using data validation based on multiple dependent columns

    Hi meetoo7, thank you for responding.


    Yes, 7 level Dropdown list doesn't look user friendly. but , I have to do it. My friend created "decomposition tree map " by using those 7 drop down list in power bi.
    I need to do it in Excel. Iam very confusing. Please help me to solve this issue. Thank you in advance.

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Create 7 dependent dropdown using data validation based on multiple dependent columns

    Without a sample output, it is not clear what you are intending to produce. Do you want to pick out those records in your main table which satisfy the selections made in the drop-downs in cells I3:O3 ? If so, would it be better to show these (and to have the actual selections) on a different sheet ?

    If this is the case, then you are not really dealing with dependent drop-downs, where the second list depends on the first, and the third depends on the choices made in the first AND the second, and so on. I think you are wanting a filtering-type drop-down, where only those records which match all of the criteria will be shown in the output. Is this the case?

    Can you also confirm that you are still using the version XL2013, and if not then please update your profile.

    Hope this helps.

    Pete

  10. #10
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Create 7 dependent dropdown using data validation based on multiple dependent columns

    Show form - start with first combobox (lefthand) work across comboboxes each will cascade and filter listbox.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    09-03-2020
    Location
    INDIA
    MS-Off Ver
    2013
    Posts
    18

    Re: Create 7 dependent dropdown using data validation based on multiple dependent columns

    Thank you so much torachan. One small issue iam facing. when I open the userform I can't go anywhere in the sheet (or) other sheets (or) other workbooks. dropdown selection in userform should not stop me to do other work. dropdown(show form) should work as "excel data validation drop down list in a particular cells". because based on dropdown i want to create few charts & summary reports in other sheets. If showform stop me to see with in the sheet (or) other sheets, So how do i see charts in other sheets. Can you please rectify it.

    Thank you in advance.

  12. #12
    Registered User
    Join Date
    09-03-2020
    Location
    INDIA
    MS-Off Ver
    2013
    Posts
    18

    Re: Create 7 dependent dropdown using data validation based on multiple dependent columns

    Hi Pete_UK,

    Thank you for responding.

    HTML Code: 
    what you said 200% correct. I need filtering-type drop-down". Please help me. If you use Office-2019 or Office 365 then also no issue. I will update my excel version.

  13. #13
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Create 7 dependent dropdown using data validation based on multiple dependent columns

    you need to decide firstly what you require as it would now appear you are talking simple validation not dependent dropdowns

  14. #14
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Create 7 dependent dropdown using data validation based on multiple dependent columns

    The attached file does what I think you want. If uses 3 formulae, and it should work okay in your version XL2013.

    First of all, I created a new sheet, Ref_data, which contains the unique values for each of the seven fields from your sample data. There is a named range for each column (using the names in row 1). These are used for the DV drop-downs. I assume that you might have new data covering up to December 2021, so I've filled in the extras for the Period column.

    There is another new sheet which I've called Output, which has the DV drop-downs in cells B2:H2 (coloured yellow), Here you can select from the appropriate list in each cell, and you can also choose "All" or leave it blank, which means that you don't want to filter on those fields. Cell B7 will tell you how many records have been found which match the criteria from the yellow cells.

    The Final sheet is as before, and I have added a Helper column (H) with a formula in cell H2 as follows:

    =IF(AND(
    IF(OR(Output!$B$2="",Output!$B$2="All"),TRUE,A2=Output!$B$2),
    IF(OR(Output!$C$2="",Output!$C$2="All"),TRUE,B2=Output!$C$2),
    IF(OR(Output!$D$2="",Output!$D$2="All"),TRUE,C2=Output!$D$2),
    IF(OR(Output!$E$2="",Output!$E$2="All"),TRUE,D2=Output!$E$2),
    IF(OR(Output!$F$2="",Output!$F$2="All"),TRUE,E2=Output!$F$2),
    IF(OR(Output!$G$2="",Output!$G$2="All"),TRUE,F2=Output!$G$2),
    IF(OR(Output!$H$2="",Output!$H$2="All"),TRUE,G2=Output!$H$2)
    ),MAX(H$1:H1)+1,"-")

    This formula has to be copied down to the bottom of your data (or beyond), and it will identify those records which match the criteria, and will allocate a unique sequential number to each such record. If you add more data to the file, then you must ensure that the formula is copied down to cover all your data.

    Back to the Output sheet, and I have used this formula in cell A11:

    =IF(ROWS($1:1)>MAX(final!$H:$H),"-",IFERROR(MATCH(ROWS($1:1),final!$H:$H,0),"-"))

    this will give a list of the rows in the Final sheet where records which match the criteria can be found, and this formula in B11:

    =IF(ISNUMBER($A11),INDEX(final!A:A,$A11),"")

    will retrieve the appropriate data from column A of that sheet. This formula can be copied across into C11:H11 to retrieve the other data for that record. Finally, the formulae in cells A11:H11 can be copied down as far as you need them, in order to bring back all the matching data. I've only copied the formulae down to row 25 in order to keep the file small, but you need to be guided by cell B7 as to how far down you need to copy them.

    So basically, you are applying filters using the drop-downs in the yellow cells, and then the filtered data appears below row 10.

    Hope this helps.

    Pete
    Attached Files Attached Files

  15. #15
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,004

    Re: Create 7 dependent dropdown using data validation based on multiple dependent columns

    Please help me. If you use Office-2019 or Office 365 then also no issue. I will update my excel version.
    For Excel 365 there is a much easier solution.
    Attached Files Attached Files
    Last edited by DJunqueira; 11-07-2021 at 04:37 AM.

  16. #16
    Registered User
    Join Date
    09-03-2020
    Location
    INDIA
    MS-Off Ver
    2013
    Posts
    18

    Re: Create 7 dependent dropdown using data validation based on multiple dependent columns

    Thank you DJunqueira,


    Your solution almost reached my requirement. But small issues are there. I randomly checked the below two scenarios.

    issue-1:

    When I put filters using dropdown :

    Period PMU MCO country Division category
    202103 ME Nordics DENMARK BCP HAIR CARE



    in formula section, list showing 47 filtered "Hair Care" (Brand) items. But Actually, there are 12 "Hair Care" items only (as per data).

    Issue -2

    When i put above same filters , in Brand section(in formula section) , showing 7 items under filtered "Category" list. But, as per data , there are 6 "Category" items only. (When we put above filter, "HOME & HYGIENE" category item is not there as per data)
    Can You Please rectify these issues.

    Note: I tested manually in "Testing" sheet. Please find attached file for your reference.
    Attached Files Attached Files
    Last edited by b_raj_kumar; 11-07-2021 at 01:53 PM. Reason: attached tested file

  17. #17
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,004

    Re: Create 7 dependent dropdown using data validation based on multiple dependent columns

    Quote Originally Posted by b_raj_kumar View Post
    Thank you DJunqueira,
    Your solution almost reached my requirement. But small issues are there. I randomly checked the below two scenarios.
    issue-1:
    When I put filters using dropdown :
    Period PMU MCO country Division category
    202103 ME Nordics DENMARK BCP HAIR CARE

    in formula section, list showing 47 filtered "Hair Care" (Brand) items. But Actually, there are 12 "Hair Care" items only (as per data).
    Easy fix, the formula is in the worksheet.
    Basically PMU-MCO-Country-Division are well related, Brand and Category needed to be related to more columns.

    Issue -2
    When i put above same filters , in Brand section(in formula section) , showing 7 items under filtered "Category" list. But, as per data , there are 6 "Category" items only. (When we put above filter, "HOME & HYGIENE" category item is not there as per data)
    Can You Please rectify these issues.
    "HOME & HYGIENE" is with "HOME CARE" not "BCP".
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    09-03-2020
    Location
    INDIA
    MS-Off Ver
    2013
    Posts
    18

    Re: Create 7 dependent dropdown using data validation based on multiple dependent columns

    Thank you so much DJunqueira. You are awesome.

+ 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. Replies: 5
    Last Post: 08-31-2021, 03:25 AM
  2. Replies: 1
    Last Post: 06-04-2018, 06:16 PM
  3. [SOLVED] Dependent dropdown data validation
    By ArnolddG in forum Excel General
    Replies: 3
    Last Post: 07-03-2014, 03:31 PM
  4. [SOLVED] Create a conditional/dependent dropdown integrating a validation query
    By SandPounder1 in forum Excel General
    Replies: 4
    Last Post: 02-21-2014, 06:01 PM
  5. [SOLVED] Dependent dropdown validation - dependent data is string
    By jnewby in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-12-2014, 07:50 PM
  6. 4 way dependent dropdown/data validation
    By Cidona in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-19-2012, 06:58 AM
  7. Excel 2007 : Dependent Dropdown (Data Validation)
    By mtpr220 in forum Excel General
    Replies: 16
    Last Post: 08-10-2011, 12:58 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