+ Reply to Thread
Results 1 to 4 of 4

Cascading Dependent Drop Down Menu's, using Named Ranges

  1. #1
    Registered User
    Join Date
    11-02-2021
    Location
    New Zealand
    MS-Off Ver
    2019
    Posts
    14

    Cascading Dependent Drop Down Menu's, using Named Ranges

    Hi there everyone,

    I am a beginner-intermediate level excel user, and I am attempting to build a feedback sheet to record, and eventually analyse, feedback from the public on services provided by local leisure facility.

    In the attachment provided, I require help in the tab labelled "Jan", and the section marked "Type of Feedback". I am using 4 drop down menu's to select the appropriate feedback based on: "Department">"Type of Feedback", (Positive, Negative, Other)>"Subject"> and "Specifics".

    My issue comes as the facility has 5 departments - Aquatics, Customer Support, Fitness, Kauri Kids, and Recreation - and some departments receive similar forms of feedback, (Positive Customer Service vs Negative Customer Service, et cetera).

    I am looking for help with a formula to use in data validation to enable me to select different departments feedback datasets that are similarly named.

    I am currently using Named Ranges, and the formula, "=INDIRECT(LEFT($I7,1) &"_" & SUBSTITUTE($J7," ","_"))", to select the correct data for the drop downs, which has worked up to creating named ranges/selecting data for "Fitness", as it shares many similar Feedback Type/Subject's as Customer Support, and I expect a similar problem with the remaining departments.

    I suspect that a formula variation of the one I am already using would work, however tweaked so that it can differentiate between departments by reviewing the first 2 letters of a named range, (i.e. for negative feedback for Fitness, "FN_Customer_Service vs negative feedback for Customer Support, CN_Customer_Service), however I am not sure how to do this and my experimenting is hitting a roadblock.

    Any help with creating such a formula, or an alternative method that I could use, would be greatly appreciated.

    Thank you for taking the time to read, and hopefully help me with, this, and have a lovely day.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Cascading Dependent Drop Down Menu's, using Named Ranges

    imo, dependent drop down via name range should be use only not much name range.
    in your case I thing it's a bit much for using name range.

    imo, using INDIRECT and search by MATCH to find the correct column name range of list might be better.

    Please review in the attatched file.

    Concept.
    Build a list of table by adding keywords at the top row of each group
    these keywords can be manually entered or use formula.

    in the sample file , I use
    sheet Aquatics , G1
    =G4&"_"&G5
    while G4 contain : Positive, G5 contain : Lab Pool
    ** Note all use keyword must be uniq, extra (unused) can be duplicate.

    After build the list of drop down. In the form area you can use ordinary drop down for Department and Type of feedback.
    Then use Department & Type of feedback to search subject by

    using data validation ; List
    E6
    =INDIRECT("'"&$C6&"'!"&
    ADDRESS(6,MATCH($D6,INDIRECT("'"&$C6&"'!1:1"),0),4)&":"&
    ADDRESS(INDIRECT("'"&$C6&"'!"&ADDRESS(3,MATCH($D6,INDIRECT("'"&$C6&"'!1:1"),0))),MATCH($D6,INDIRECT("'"&$C6&"'!1:1"),0),4))

    and for Specifics
    F6
    =INDIRECT("'"&$C6&"'!"&
    ADDRESS(6,MATCH($D6&"_"&$E6,INDIRECT("'"&$C6&"'!1:1"),0),4)&":"&
    ADDRESS(INDIRECT("'"&$C6&"'!"&ADDRESS(3,MATCH($D6&"_"&$E6,INDIRECT("'"&$C6&"'!1:1"),0))),MATCH($D6&"_"&$E6,INDIRECT("'"&$C6&"'!1:1"),0),4))

    Regards.


    Note: In the sample file, only Aquatics and Customer Support was done, you can add more departments.
    Attached Files Attached Files
    Last edited by menem; 01-27-2022 at 03:02 AM. Reason: add note

  3. #3
    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,007

    Re: Cascading Dependent Drop Down Menu's, using Named Ranges

    If it ain't broke why fix it? A minor change: you could limit "Feedback" to a simply list as it is Department independent.
    Last edited by JohnTopley; 01-27-2022 at 09:12 AM.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Cascading Dependent Drop Down Menu's, using Named Ranges

    I have had a similar issue to work with, but not on the scale you present. I have a method using pivot tables and vba. You have one pivot table per level of cascading and the vba triggers filters on the pivot tables. Each pivot table is overlaid with a named dynamic range from which the data validation is performed.

    So instead of having dozens of named ranges, you will have 4.

    If you insist on using named ranges, I can envision something that is theoretically possible.

    If you had only two cascades, I could envision where you would go across the column headers to find the department, then get the facility from the rows under that column header. This could be done easily with Excel Tables and Indirect.

    To get a third level such as type comment, negative or positive, we'd have to go into a third dimension. Excel tables don't have that capability. Maybe the third dimension could be two sets of tables, one for positive and one for negative, but now we are into "hard coding" the results.

    I am not able to wrap my mind around expanding the model to 4 dimensions, but I don't think it could be done without hard coding too.

    So, back to the pivot model. Here is an example what it would look like. Each of these dropdowns would be overlaid with a named dynamic range. Even here, I "hardcoded" positive and negative as the only two choices, otherwise I would expand to a 4th pivot table. This setup allows you to drill down to the facility and then select positive or negative.

    To further protect the results, I would have the users fill in a form where the next field is opened upon the selection of the previous field, then then write the results to a normalized data table. I would probably spread the results out across three normalized tables for person, feedback and action and link them using a uniquely generated feedback number.

    OK, now we are talking about using a database. You are going to have to do a lot of programming in Excel to get a very specific and limited functionality whereas a database form with a few subforms and queries could be done more easily (it's still difficult), and it would be a lot more flexible.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ 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. [SOLVED] Dependent Drop-down with Dynamic Named Ranges
    By LKERN in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-20-2020, 12:42 PM
  2. Error in cascading (dependent) Excel drop down list
    By tigergutt in forum Excel General
    Replies: 7
    Last Post: 02-24-2020, 10:35 AM
  3. Dependent Drop-down menu without named ranges
    By olga6542 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-17-2019, 12:02 AM
  4. Bottom up hierarchy in dependent cascading drop down list
    By sergeipol in forum Excel General
    Replies: 8
    Last Post: 11-06-2018, 02:19 AM
  5. Creating Dependent or Cascading Drop Downs
    By Draco119 in forum Excel General
    Replies: 3
    Last Post: 08-31-2018, 11:21 AM
  6. Dependent drop down lists -- without named ranges?
    By vicken_a in forum Excel General
    Replies: 3
    Last Post: 03-30-2018, 12:52 PM

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