title: Excel VBA Dynamic data validation drop downs with multiple criteria ranking
body: <p>I am trying to create a dynamic drop down data validation list that will rank multiple criteria (#2 or more) from a worksheet, there are 300 items in my list and I want to rank them based on information in another worksheet in a table.</p>
  
  <p>Based on the rank (1 to 300) I would like the drop down data validation list to contain top 10, top 25 and top/bottom # values calculated from their rank. I don't mind helper columns. If the data/table I am ranking from changes, and/or if I want to add a criteria I would like the top 10, top 25 etc to change accordingly.</p>
  
  <p>I have recorded with the macro recorder when I use the advanced filter and also the top 25 in this case values.</p>
  
  <pre><code>Sub Makro2()
  Selection.AutoFilter
  Range("T[#All]").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
      Range("A1:J3"), Unique:=False
  
  Range("T[[#Headers],[2017]]").Select
  ActiveSheet.ShowAllData
  
  Selection.AutoFilter
  
  ActiveSheet.ListObjects("T").Range.AutoFilter Field:=2, Criteria1:="25", _
      Operator:=xlTop10Items
  End Sub
  </code></pre>
  
  <p>Is this possible in Excel 2016 with or without VBA?</p>
  
  <p>Edit: I found this thread <a href="https://stackoverflow.com/questions/45332607/data-validation-drop-down-list-not-auto-updating">Data Validation drop down list not auto-updating</a> and this code in that thread could be what I am looking for.</p>
  
  <pre><code>Option Explicit
  
  Private Sub Worksheet_Change(ByVal Target As Range)
  ' Ensure all lists are made from tables and that these tables are named
  ' in the Name Manager.
  ' When creating your Data Validation List, instead of selecting a range
  ' in 'Source', click within 'Source' and press 'F3'. Finally select your
  ' tables name.
  Dim strValidationList As String
  Dim strVal As String
  Dim lngNum As Long
  
  On Error GoTo Nevermind
  strValidationList = Mid(Target.Validation.Formula1, 2)
  strVal = Target.Value
  lngNum = Application.WorksheetFunction.Match(strVal, ThisWorkbook.Names(strValidationList).RefersToRange, 0)
  
  ' Converts table contents into a formula
  If strVal &lt;&gt; "" And lngNum &gt; 0 Then
      Application.EnableEvents = False
      Target.Formula = "=INDEX(" &amp; strValidationList &amp; ", " &amp; lngNum &amp; ")"
  End If
  
  Nevermind:
      Application.EnableEvents = True
  
  End Sub
  </code></pre>
  
  <p>Update:</p>
  
  <p>I am using the LARGE function to get the top 15 values of Table1. I am then using INDEX and MATCH to find the names of the top 15 values (column 2).</p>
  
  <p>I am then using the OFFSET function and a NAMED RANGE to get a data validation list that auto updates when I add something to the bottom of the list.</p>
  
  <p>Now I want the data validation list to be dependent on the first drop down. How can I achieve this? </p>