+ Reply to Thread
Results 1 to 5 of 5

Multiple Criteria Dependant Drop Down, Less Than Equal To, NOT Sorted

  1. #1
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686

    Multiple Criteria Dependant Drop Down, Less Than Equal To, NOT Sorted

    Excel 2010

    I have a range with 9 columns. Of these 9 columns 4 are my concern. They are not all adjacent and I much prefer to NOT change their location with respect to the other column as users are accustomed to the order they appear.

    I can apply any named ranges and or VBA to obtain a solution.
    I could convert this to an Excel Table. The author prefers I don’t but I can. (It has 3 header rows - arrrgh)
    I CANNOT order these in any Ascending or Descending order by ANY of these columns.

    Columns of interest and criteria are:
    SIZE – I wish to return a list of SIZES that meet the criteria
    DIM1 = criteria, numeric known via formula, NOT dependent on user selected TYPE
    DIM2 <=criteria, numeric known via formula, NOT dependent on user selected TYPE
    TYPE = criteria, User Selected

    DIM2 example, criteria known via formula = 27.59, must find a DIM2 Less Than or Equal To 27.59.

    The only solution I can come up with is, when the user select the TYPE, use the WorksheetChange Event to loop through the rows of data to build a Data Validation list. This is not my first choice since the user could copy & paste multiple TYPE entries and a Target.Count >1 just opens a whole new can of worms.

    The file currently has a dependent drop down; user selects TYPE and then selects SIZE but nothing stops them from selecting a Size which has a Dim1 or Dim 2 not meeting criteria.

    TIA

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,356

    Re: Multiple Criteria Dependant Drop Down, Less Than Equal To, NOT Sorted

    Attach a sample workbook.

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686

    Re: Multiple Criteria Dependant Drop Down, Less Than Equal To, NOT Sorted

    Correction to Criteria:
    SIZE – I wish to return a list of SIZES that meet the criteria
    DIM1 >= criteria, numeric known via formula, NOT dependent on user selected TYPE
    DIM2 <=criteria, numeric known via formula, NOT dependent on user selected TYPE
    TYPE = criteria, User Selected

    See attached. I saved as xlsx for your security but if you want to enable macros it's find with me.

    Sheet “table” ---> the table to search in
    Sheet “dropdown” ---> Sample of User Selected Type, known Dim1 & Dim2 and Desired Drop Down list
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,356

    Re: Multiple Criteria Dependant Drop Down, Less Than Equal To, NOT Sorted

    In E4

    =IFERROR(INDEX(ListSize,SMALL(IF((ListType=$A4)*(ListMaxDim1>=dropdown!$B4)*(ListMinDim2<=dropdown!$C4),ROW(table!$A$4:$A$19)-ROW($A$4)+1,""),COLUMNS($E$4:E4))),"")

    Enter with Ctrl+Shift+Enter

    Copy across (to at least column I) and down
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686

    Re: Multiple Criteria Dependant Drop Down, Less Than Equal To, NOT Sorted

    Awesome!

    Thanks a million!

+ 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] Help in creating multiple dependant drop downs
    By ahm3d in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-04-2016, 07:49 AM
  2. Replies: 1
    Last Post: 09-04-2014, 12:14 PM
  3. Please help with multiple dependant drop down lists
    By kartoshka in forum Excel General
    Replies: 6
    Last Post: 05-12-2013, 07:59 AM
  4. Multiple Dependant Drop Down Lists
    By shoo0204 in forum Excel General
    Replies: 19
    Last Post: 03-08-2013, 04:49 PM
  5. Multiple drop dependant drop downs in single cell
    By LittleJerry in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-07-2012, 02:21 PM
  6. Multiple dependant drop downs
    By paconovellino in forum Excel General
    Replies: 11
    Last Post: 05-02-2012, 05:05 PM
  7. Replies: 3
    Last Post: 07-06-2010, 05:30 AM

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