Data Validation Drop Down List - Allow Duplicates

[ronie85]

Hi I am hoping someone will know this.

I have a data validation list for a change of a product outside of the generic product, meaning I want the first item on the list to be a = to the main generic product (which changes to other products on the list) and the rest of the items are all the products including the default generic product.
This means that it is defaulted until I manually change it to something else on the list.

The list should be;

= to Product 3
Product 1
Product 2
Product 3
Product 4
Product 5

The problem is that the list doesnt allow duplicates therefore the list like this;

Product 3
Product 1
Product 2
Product 4
Product 5

I would like it to be like the first list so that it is always defaulted to the generic product and the drop down box is only used to change the product.

At the moment when I set it to the first line, = Product 3, although this is the first selection, it is actually at the 4th line Product 3 therefore when the generic selection is changed elsewhere in the sheet, the list remains the same.

Can anyone help me to allow the data validation list to show all selections including the duplicate so that this works as I need it to?

 

[6StringJazzer]

How are you creating the data validation list?

I can easily create the situation you are describing, so I have a feeling there is a lot you are not describing.

Set the cell to a default of
=C4
Create the data validation list

Now select from the dropdown:

 

[ronie85]

Hopefully this explains it better

The blue list is the default list for which the red list should always default to this unless changed otherwise.
Cell G12 of List 2 (the list in question) equals the drop down selection of Cell B3 for List 1.

As you can see, List 2 should show 2 x Product 3's as it appears as the default linked to B3, and it also appears in the actual list G15.
Though the list excludes the 2nd reference therefore it can't sit naturally defaulted to B3 as it won't recognize both of the same name.

 

[Artik]

Zeszyt1
	A	B	C	D	E	F	G	H	I
1							Product 1		A
2		For Product 3:					Product 2		B
3		Product 3					Product 3		C
4							Product 4
5				For Product 2:			Product 5
6				Product 2
7	For Product 1:
8	Product 1
9				Other Validation list:
10				B
Arkusz1

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=$G$1:$G$5
D6	List	=$G$1:$G$5
A8	List	=$G$1:$G$5
D10	List	=$I$1:$I$3

Assuming that there is such a moment when the form (sheet) is prepared for re-filling. I assumed that at this point we want to restore the default values for each of the cells in yellow. I assigned the following macro to the button on the sheet.

Sub RestoreToDefault()
    Dim wks As Worksheet
    Dim rngSpecimen As Range
    Dim rng As Range
    Dim rngSource As Range
    Dim lDefaultPos As Long

    Set wks = ActiveSheet

    With wks
        
        Set rngSpecimen = wks.Range("B3")

        'the range of cells that are the source of the validation list
        Set rngSource = wks.Range(rngSpecimen.Validation.Formula1)
        
        'loop through all cells that have the same validation list as cell B3
        For Each rng In rngSpecimen.SpecialCells(xlCellTypeSameValidation)
            lDefaultPos = 0

            'defining a default validation list item for a given cell
            Select Case rng.Address(0, 0)
            Case "B3"
                lDefaultPos = 3
            Case "D6"
                lDefaultPos = 2
            Case "A8"
                lDefaultPos = 1
            End Select

            If lDefaultPos > 0 Then
                'if the default value has been defined
                rng.Value = rngSource.Cells(lDefaultPos).Value
            Else
                'No default value is defined, so clear the cell
                rng.ClearContents
            End If

        Next rng
        
    End With
End Sub

Artik

 

[6StringJazzer]

How is the data validation for List 2 in cell G3 defined? Please give the actual formula.

 

[ronie85]

It is a standard List = formula of =$G$12:$G$18