Results 1 to 2 of 2

Thread: Multiple cascading drop-downs, vba, form tool?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2007
    Posts
    353
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Multiple cascading drop-downs, vba, form tool?

    Hi,

    I'm seeking to effect the most robust way a user can to drill through 57k rows of organisational hierarchy in a cascade style.


    • My ask is to create a tool for users to log business services. So it needs to be quick and robust and intuitive

    • There are 8 levels of hierarchy from Legal Entity down to cost center

    • For any given service I want to capture the code of the org unit(s) providing and org unit(s) receiving

    • "Org unit" can describe the code of any of the 8 hierarchy levels

    • Codes at any given org level are unique, but all occurrences of any given n+1 department code are common across LEs where that department exists within a given LE



    What's the best way to do this?


    • For capturing the information I propose an excel table I can pick up later in bulk and make sense of with Power Query. I would like the user to log rows where the first column is "Side" i.e. provider/receiver and then fill out columns to the right as required which describe hierarchy (LE, BD, BU etc)

    • For presenting input to users I am aware of in-cell validation and also wondering if a pop-up excel form might be of genuine use here

    • Going round in circles at the moment with Power Query summaries of the 57k row hierarchy and indexed slices versus dynamic named ranges. I was hoping to effect something elegant and simple using a column name/cell value driving data validation to a table name. But I feel like I need to jump to a table and filter it



    A steer on the above would be much appreciated. If I can do it in Excel then I want to do it that way. If I need to do it in Access I will but this is considered witchcraft around here and I remember the front end form stuff being a bit of a pig...


    Many Thanks,

    Andrew
    Last edited by andydtaylor; Jul 29th, 2019 at 09:03 AM.

  2. #2
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple cascading drop-downs, vba, form tool?

    Setup your lookup data well and it is relatively straight-forward.

    See https://contextures.com/xlDataVal02.html

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •