+ Reply to Thread
Results 1 to 7 of 7

Dependent Data Validation list using dynamic range filtered from multiple criterion

  1. #1
    Registered User
    Join Date
    07-31-2019
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Dependent Data Validation list using dynamic range filtered from multiple criterion

    I'm trying to get the sheaves to automatically create a filtered list dependent on selected motor and large sheave bore input on "user inputs" tab. the sheaves match based on bushing type or bore diameter. I tried to create the ranges for the sheaves lists using advanced filter based on table[#all] as list range, the listed criteria ranges listed in the criteria range tab and copy to lists tab. This ultimately did no work right, I also don't want "Item Number and Description" to show up in the list. I would like these ranges to update automatically If new sheaves or bushing get added to table 5 or bushings list. Can someone please help me out with tis or help give a different method of doing this. If this requires a vba macro I could really use help because I have very little experince in coding. This got handed off to me and I hardly understand the complicated nested index and match functions that popultate the selected bushing. Thank you
    Last edited by mportner785; 08-01-2019 at 02:24 PM.

  2. #2
    Registered User
    Join Date
    07-31-2019
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Re: Dependent Data Validation list using dynamic range filtered from multiple criterion

    Did the sheet get uploaded? I can't see where it is attached
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    833

    Re: Dependent Data Validation list using dynamic range filtered from multiple criterion

    Could you attach a sample workbook:
    Click on Go Advanced (below the Reply box) > click Manage Attachments to open the upload window.

    Edit: ah, you already did that

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,317

    Re: Dependent Data Validation list using dynamic range filtered from multiple criterion

    Welcome to the forum.

    You uploaded a .zip file. Please upload an Excel workbook. It does NOT need to be your entire project, just a copy of the relevant data to illustrate the issue you wish to resolve.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    07-31-2019
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Re: Dependent Data Validation list using dynamic range filtered from multiple criterion

    Here is an older version that seems to be the right size and the snipit is of the criteria ranges.Capture.PNG
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-31-2019
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Re: Dependent Data Validation list using dynamic range filtered from multiple criterion

    I guess I should rephrase. When I select a bore size I want it to return a data validation list with all the possible sheave combinations that go with the bore size. Some Sheaves dont need a bushing and will match on bore size some require a bushing of differnt types depending the style and bore size of the bushing. How do I get those to show up in a dependent data validation list when the bore size or motor (depends on bore size as well)is selected.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,258

    Re: Dependent Data Validation list using dynamic range filtered from multiple criterion

    The current dependent drop downs refer to the 'Sheaves' sheet, however there isn't any information about the HP of the motor on that sheet. My thinking is that you would need to include a column stating the HP of the motor on the 'Sheaves' sheet in order to accomplish what you want. Looking at the motors sheet I would guess that a motor is can be matched to a bore up to a certain diameter, as in a 10 HP motor can be matched to a bore up to 1.375, however I would want to be sure about this before producing a column.
    Also in the example the values that are shown in A8:A9 do not seem to match the bore (1.500). I did not check the values in the other blue columns of rows 8:9. It is always helpful to show expected values so that contributors can attempt to replicate those values using formulas/code.
    Once the HP values are added to the 'Sheaves' sheet and the expected values are placed (manually if necessary) in rows 8:9 of the 'User Inputs' sheet please re-upload the file.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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] Multiple Condition Sum Between Dates that is dependent on a Data Validation List.
    By JarSanYou in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-11-2018, 10:02 AM
  2. Dynamic dependent Validation List based in multiple tables and formulas
    By se7en___ in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-30-2018, 04:47 PM
  3. Dependent Data Validation List with Dynamic Range
    By sakmsb in forum Excel General
    Replies: 1
    Last Post: 06-09-2015, 12:42 PM
  4. dynamic dependent validation list
    By ComeOnBee in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-06-2015, 04:58 AM
  5. [SOLVED] Dynamic dependent data validation list
    By Masun in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-03-2014, 10:34 AM
  6. [SOLVED] Can't use Numbers in named range list for Dependent data validation lists
    By Sam Kuiper in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 11-11-2012, 08:26 PM
  7. data validation--multiple dependent list
    By Michael in forum Excel General
    Replies: 9
    Last Post: 05-01-2006, 08:15 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