+ Reply to Thread
Results 1 to 20 of 20

how to adjust the dependent drop-down list to 4 levels of multiple words per cell

  1. #1
    Forum Contributor daliye's Avatar
    Join Date
    05-28-2023
    Location
    North Africa
    MS-Off Ver
    microsoft excel, latest 365, 64-bit!
    Posts
    310

    Question how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    Hi,
    Please, I do not know how to adjust the dependent drop-down list to 4 levels of multiple words per cell.
    1st row of the sheet A1, B1, C1, and D1 includes the drop down list I've created.
    I've created the tables and the defined names.
    I've watched many YouTube videos but was not able to adjust and apply it to my case.
    I hope you may guide me on what could be done to adjust and fix it to work in a dependant way.
    The desired results will be like this:
    • Column A has several main categories, and each row includes the main category for that associated row.
    • I want to set it so that if I choose an item from column A, I want the next column list in column B to only contain the associated items from column B that are associated with column A.
    • For example, if I choose "Data Services" in column A first cell, I mean A1. I want the next drop-down menu to only include the items associated with it, which is in this example, 5 items.
    • So on for dependant drop downlist in column C and column D.
    • Also I want to do it for the whole table.
    Attached Files Attached Files
    Last edited by AliGW; 10-16-2023 at 02:54 AM.
    thnx!
    d!
    microsoft 365 apps for enterprise

  2. #2
    Forum Contributor daliye's Avatar
    Join Date
    05-28-2023
    Location
    North Africa
    MS-Off Ver
    microsoft excel, latest 365, 64-bit!
    Posts
    310

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    I do hope if someone may guide me

  3. #3
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,505

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    In this example, column H, I, J and K are hidden. That is the dropdown reference.
    Attached Files Attached Files
    Last edited by JEC.; 08-31-2023 at 03:53 AM.

  4. #4
    Forum Contributor daliye's Avatar
    Join Date
    05-28-2023
    Location
    North Africa
    MS-Off Ver
    microsoft excel, latest 365, 64-bit!
    Posts
    310

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    Thanks a lot, JEC for your help.
    Excellent.

  5. #5
    Forum Contributor daliye's Avatar
    Join Date
    05-28-2023
    Location
    North Africa
    MS-Off Ver
    microsoft excel, latest 365, 64-bit!
    Posts
    310

    FormulaTo List Unique Values From Next Column Associated With Cell In Current Column

    Hi,

    First of all, the previous solution worked brilliantly, but I am afraid that adding more columns led to other major changes that need to be done in the production file.

    There is not something wrong with the solution but it is from my side, apologized for that.

    These changes will not be possible to be done because the file is already being used in another place.

    So is it possible to adjust the solution to be a drop-down menu in a single row and add formulas to this drop-down menu that achieve the target?

    The final purpose is to use these formulas in a drop-down list.

    For example, column F has a header Service, this column will be a drop list for unique values from column A.

    column G has a header ServiceType, this column will be a drop list for unique values from column B, but only those associated with the cell that has been chosen from column F.

    So on for the rest.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2508 (Windows 11 Home 24H2 64-bit)
    Posts
    91,943

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    Pleas remove the solved tag as explained in my PM reply to you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2508 (Windows 11 Home 24H2 64-bit)
    Posts
    91,943

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    As you were unable to work it out, I have removed the SOLVED tag for you.

  8. #8
    Forum Contributor daliye's Avatar
    Join Date
    05-28-2023
    Location
    North Africa
    MS-Off Ver
    microsoft excel, latest 365, 64-bit!
    Posts
    310

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    Somebody...Anybody
    Last edited by Glenn Kennedy; 02-08-2024 at 04:09 AM. Reason: Irrelevant content removed.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2504
    Posts
    19,415

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    Something that is close to the displayed desired result could be achieved using a pivot table.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  10. #10
    Forum Contributor daliye's Avatar
    Join Date
    05-28-2023
    Location
    North Africa
    MS-Off Ver
    microsoft excel, latest 365, 64-bit!
    Posts
    310

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    Thanks a lot for your help.
    But, if I want it to be in one row only, is it possible?

  11. #11
    Forum Contributor daliye's Avatar
    Join Date
    05-28-2023
    Location
    North Africa
    MS-Off Ver
    microsoft excel, latest 365, 64-bit!
    Posts
    310

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    I am still looking for a solution, and I found this video here:
    https://www.youtube.com/watch?v=r0DTFodQYJQ
    I can not adjust this technique to apply it to the file.
    Please, may someone guide me?
    The file used in the video is attached
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2504
    Posts
    19,415

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    Looking at the file there are multiple hidden columns, B:I on the Inputs sheet, which is something that you have already rejected in JEC.'s proposed solution.

  13. #13
    Forum Contributor daliye's Avatar
    Join Date
    05-28-2023
    Location
    North Africa
    MS-Off Ver
    microsoft excel, latest 365, 64-bit!
    Posts
    310

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    I've misunderstood the video and also misunderstood your solution, and I owe you and @JEC. an apology.
    I am sorry for my mistake.
    Please, let me rephrase my request.
    I want to have only one row where I can do a selection.
    So is it possible to place this row on the top of a worksheet, and add your working process calculation in another sheet?
    I mean I can not add both places in the same sheet.

  14. #14
    Forum Contributor daliye's Avatar
    Join Date
    05-28-2023
    Location
    North Africa
    MS-Off Ver
    microsoft excel, latest 365, 64-bit!
    Posts
    310

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    Quote Originally Posted by JEC. View Post
    In this example, columns H, I, J, and K are hidden. That is the dropdown reference.
    Hi JEC.
    Thanks a lot for your solution.
    If your time allows, I've recreated my original file and allocated a place for your solution.
    I am having a little problem. When I add more rows for the drop-down items (extend it), it does not work.
    L4:O4 for example.
    Could it be fixed?

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2504
    Posts
    19,415

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    ...I've recreated my original file...
    I feel that we are more likely to be able to help if we could see the recreated file.

  16. #16
    Forum Contributor daliye's Avatar
    Join Date
    05-28-2023
    Location
    North Africa
    MS-Off Ver
    microsoft excel, latest 365, 64-bit!
    Posts
    310

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    @JeteMc
    Thanks a lot, my friend, for the fast reply.
    Reputation added!
    I am a bit worried about attaching my actual file, and I hope you understand how that would be critical for me.
    If possible, is it possible to use the file in post #3 here by JEC.:
    https://www.excelforum.com/excel-gen...ml#post5865729
    I added the solution in the same place as shown in the file.
    Please accept my apologies, and I hope to find a solution.

  17. #17
    Forum Contributor daliye's Avatar
    Join Date
    05-28-2023
    Location
    North Africa
    MS-Off Ver
    microsoft excel, latest 365, 64-bit!
    Posts
    310

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    Hi,

    I've tried a lot to adjust it, and finally, I did it in a table correctly.

    The original data is in Table1.

    The key was using the Defined Names approach, with INDIRECT, SUBSTITUTE, and ARRAYS.

    The 1st problem I've got was that too many "-" are associated with many items.

    To get rid of the "-", I've copied the contents of Table1 to Table2 using the following formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in F11.

    1.png

    Then, I collected each column with its next column to create an array for each item as follows:

    Now, the first step is to create the arrays for the columns:

    Main .... Selected K10:K11, and click this: Formulas > Defined Names > Create from Selection > ticked Top Row.
    Type .... Selected K21:K22, and click this: Formulas > Defined Names > Create from Selection > ticked Top Row.
    Sub .... Selected K46:K47, and click this: Formulas > Defined Names > Create from Selection > ticked Top Row.

    These three steps created the desired defined name ranges to be used as arrays later in formulas (I will add the "#" when I need it as an array).

    2.png

    Now, the second step is to create the arrays for the different contents in each column:

    Select K11:L18, and click this: Formulas > Defined Names > Create from Selection > ticked Left Column.

    Select K22:L43, and click this: Formulas > Defined Names > Create from Selection > ticked Left Column.

    Select K47:L70, and click this: Formulas > Defined Names > Create from Selection > ticked Left Column.

    3.png

    Again, These three steps created the desired defined name ranges to be used as arrays later in formulas (I will add the "#" when I need it as an array).

    Now ... we move to the drop-down list table, Table3.
    Column F, which is Main, will include the following in the Data Validation Source:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Column G, which is Type, will consist of the following in the Data Validation Source:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Column H, which is Sub, will include the following in the Data Validation Source:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Column H, which is Company, will include the following in the Data Validation Source:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you reached this line, thanks for your time helping me.

    Now, what I do hope I can find a solution for are:

    1. How do you restore the "-" to the selections of the drop-down list? Is it possible to use something like the SUBSTITUTE to replace the result that contains the "No" with "-"?

    2. After working with the drop list table, I found that if I changed the 1st column, the rest still existed. If I changed one by one, it was still, to some extent,t not adjusted, so I want to use the conditional format to highlight the other cells if anything is incorrect. I could only do the conditional format between 2 columns next to each other using the formula if the result in conditional format <> the indirect formula result.
    But this is not what I am looking for, as I was wondering if any cell in a row in the table contains any item that is not associated with the rest of the items in the same row to have a highlighted entire row.
    I thought about using something like XLOOKUP and linking it to the other arrays in the row, but I could not adjust it and kept getting an error.
    For example, if you choose an item from 1st column and do the same with the other columns, this is correct, but if you change only the item in the first column, then the 2nd item will have an alert, but the third and fourth items will not be wrong because the second item is still correct from the aspect of third and fourth items (I hope that I did not lose you)
    The bottom line is that unless all cells in the entire row are associated with each other and correct, I want the whole row to be highlighted in red.
    4.png
    Attached Files Attached Files
    Last edited by daliye; 02-07-2024 at 05:31 AM.

  18. #18
    Forum Contributor daliye's Avatar
    Join Date
    05-28-2023
    Location
    North Africa
    MS-Off Ver
    microsoft excel, latest 365, 64-bit!
    Posts
    310

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    I do hope you can help me.

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

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    Is it possible to use something like the SUBSTITUTE to replace the result that contains the "No" with "-"?
    This has no value.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  20. #20
    Forum Contributor daliye's Avatar
    Join Date
    05-28-2023
    Location
    North Africa
    MS-Off Ver
    microsoft excel, latest 365, 64-bit!
    Posts
    310

    Re: how to adjust the dependent drop-down list to 4 levels of multiple words per cell

    Thanks a lot for your reply.
    Quote Originally Posted by DJunqueira View Post
    This has no value
    Do you mean the formula is wrong?
    Or do you mean this is the solution?
    Or do you mean it makes no sense to you?
    Please excuse my poor English.
    Thanks a lot for your valuable time helping me.

+ 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. Drop-down list with multiple levels/grouped by category
    By pluffpluff in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-17-2023, 12:38 PM
  2. Creating Dependent Drop Down List with two words and illegal characters
    By Raehan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-03-2019, 03:42 PM
  3. Replies: 1
    Last Post: 11-27-2017, 02:31 PM
  4. Auto-adjust stock levels based on drop-down input
    By generalxmayhem in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2016, 03:27 PM
  5. Auto-adjust stock levels based on drop-down input
    By generalxmayhem in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-25-2016, 12:09 PM
  6. 4 Levels of Dependent Drop Down Boxes
    By adray13 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-24-2015, 12:17 PM
  7. Replies: 8
    Last Post: 02-12-2014, 05:16 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