Unique dynamic data validation list

Polanskiman

Board Regular
Joined
Nov 29, 2011
Messages
112
Office Version
  1. 365
  2. 2016
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile
Hello,

I have 2 sheets. Sheet 1 is the source data, Sheet 2 is a user sheet. I need in Sheet 2 multiple drop down lists as following:

Book1
DE
3Product nameElement
4Product 1Mn
5Product 2Mn
6Product 3Zn
7Product 4Zn
8Product 5B
9Product 6Cu
10Product 7Cu
11Product 8Mo
12Product 9Mo
13Product 10Ni
14Product 11Co
Sheet1


Book1
DE
3ProductElement
4Dropdown list of all Mn products from sheet 1Mn
5Dropdown list of all Zn products from sheet 1Zn
6Dropdown list of all B products from sheet 1B
7Dropdown list of all Cu products from sheet 1Cu
8Dropdown list of all Mo products from sheet 1Mo
9Dropdown list of all Ni products from sheet 1Ni
10Dropdown list of all Ni products from sheet 1Co
Sheet2


Obviously I could make very easily multiple ranges for each drop down, but I would like to have a single formula than I can use for all drop down lists. How can I achieve that?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for Sheet2 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Click on a cell in column D of Sheet2.
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim LastRow As Long, srcWS As Worksheet, arr As Variant, i As Long, dic As Object, key As Variant
    Dim cnt As Long, fVisRow As Long, rng As Range, val As String, x As Long: x = 4
    Set srcWS = Sheets("Sheet1")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    arr = srcWS.Range("E4:E" & LastRow).Value
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    If Target.Row >= 4 Then
        Application.ScreenUpdating = False
        Set dic = CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(arr, 1)
            If Not dic.Exists(arr(i, 1)) Then
                dic.Add arr(i, 1), Nothing
            End If
        Next i
        For Each key In dic.keys
            With srcWS
                .Cells(3, 4).CurrentRegion.AutoFilter 2, key
                fVisRow = .Rows("2:" & LastRow).SpecialCells(xlCellTypeVisible).Row
                cnt = .[subtotal(103,A:A)] + 2
                For Each rng In .Range("D4:D" & LastRow).SpecialCells(xlCellTypeVisible)
                    If val = "" Then val = rng Else val = val & "," & rng
                Next rng
                With Range("D" & x).Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=val
                End With
            End With
            val = ""
            x = x + 1
        Next key
    End If
    srcWS.Range("D3").AutoFilter
    Application.ScreenUpdating = True
End Sub
 

Polanskiman

Board Regular
Joined
Nov 29, 2011
Messages
112
Office Version
  1. 365
  2. 2016
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile
Thank you but can't this not be done without VBA?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I know of know way to do what you want with a single formula. You would either need a formula for each dropdown, or VBA.
 

Polanskiman

Board Regular
Joined
Nov 29, 2011
Messages
112
Office Version
  1. 365
  2. 2016
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile
I know of know way to do what you want with a single formula. You would either need a formula for each dropdown, or VBA.
I suppose you mean you "don't know"?

By single formula I meant the same formula for each dropdown so that I don't need to customize the formula for each of them.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

By single formula I meant the same formula for each dropdown so that I don't need to customize the formula for each of them.
Well, sort of a cross. If you did something like this on Sheet1

Polanskiman_2.xlsm
DEFGHIJ
1
2
3Product nameElement
4Product 1MnMnProduct 1Product 2
5Product 2MnZnProduct 3Product 4
6Product 3ZnBProduct 5
7Product 4ZnCuProduct 6Product 7
8Product 5BMoProduct 8Product 9
9Product 6CuNiProduct 10
10Product 7CuCoProduct 11
11Product 8Mo
12Product 9Mo
13Product 10Ni
14Product 11Co
Sheet1
Cell Formulas
RangeFormula
G4:G10G4=UNIQUE(E4:E14)
H7:I8,H6,H9:H10,H4:I5H4=TRANSPOSE(FILTER($D$4:$D$14,$E$4:$E$14=G4,""))
Dynamic array formulas.


Then on Sheet2 you could have the same DV formula for all rows.
Here I have used the spill ability of Excel 365. See more on this below.

Polanskiman_2.xlsm
DE
3ProductElement
4Mn
5Zn
6B
7Cu
8Mo
9Ni
10Co
Sheet2
Cells with Data Validation
CellAllowCriteria
D4:D10List=INDEX(Sheet1!$H:$H,MATCH(E4,Sheet1!$G:$G,0))#


1606355063456.png


If this general idea is any use and it needs to operate in versions that do not spill then it still can be done but with a fair bit more work on Sheet1.
Post back if you want more about that.
 

Polanskiman

Board Regular
Joined
Nov 29, 2011
Messages
112
Office Version
  1. 365
  2. 2016
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile
@Peter_SSs
The above solution is really sweet only issue is if I want to make this the most compatible as possible with older versions of Excel this will not work I suppose due to the spill, right? What's the lowest version of Excel the solution will work on?
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
What's the lowest version of Excel the solution will work on?
Excel 365, but as i said above ..
If this general idea is any use and it needs to operate in versions that do not spill then it still can be done but with a fair bit more work on Sheet1.
Post back if you want more about that.
.. so for earlier versions

Polanskiman_4.xlsm
DEFGHIJKLM
1
2
3Product nameElement
4Product 1MnMn2Product 1Product 2   
5Product 2MnZn2Product 3Product 4   
6Product 3ZnB1Product 5    
7Product 4ZnCu2Product 6Product 7   
8Product 5BMo2Product 8Product 9   
9Product 6CuNi1Product 10    
10Product 7CuCo1Product 11    
11Product 8Mo       
12Product 9Mo       
13Product 10Ni       
14Product 11Co       
Sheet1
Cell Formulas
RangeFormula
G4:G14G4=IFERROR(INDEX(E$4:E$14,MATCH(0,INDEX(COUNTIF($G$3:G3,E$4:E$14)+(E$4:E$14=""),0),0)),"")
H4:H14H4=IF(G4="","",COUNTIF(I4:M4,"?*"))
I4:M14I4=IFERROR(INDEX($D$4:$D$14,AGGREGATE(15,6,(ROW($D$4:$D$14)-ROW($D$4)+1)/($E$4:$E$14=$G4),COLUMNS($I4:I4))),"")



Polanskiman_4.xlsm
DE
1
2
3ProductElement
4Mn
5Zn
6B
7Cu
8Mo
9Ni
10Co
Sheet2
Cells with Data Validation
CellAllowCriteria
D4:D10List=OFFSET(INDEX(Sheet1!$I:$I,MATCH(E4,Sheet1!$G:$G,0)),,,,INDEX(Sheet1!$H:$H,MATCH(E4,Sheet1!$G:$G,0)))
 
Solution

Polanskiman

Board Regular
Joined
Nov 29, 2011
Messages
112
Office Version
  1. 365
  2. 2016
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile
Yes that seems seriously more involved. It does works as expected though. Thumbs up for that.
Question regarding the OFSSET formula or the INDEX formula for the first option. Why are you using full column references rather than just a specified range?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,870
Messages
5,627,366
Members
416,245
Latest member
Xterminat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top