Data validation list - the order of computing formulas

[Sepkong]

Hello,

the good example of drop down list is here:
Prevent duplicates in cells where there is a dropdown list

It is not working when typing allowed value, for example "peaches" and pressing enter on the keyboard.

The value "peaches" is accepted when it is choose directly from the list or typed by keyboard and mouse click on any other cell.

Why the way of typing right value and pressing enter won't work?

(it looks like after pressing enter, the data validation compares value "peaches" with dynamic list where there are not "peaches" anymore.) - but can this be omitted in some way?

 

[mumps]

When setting up the data validation list, click the "Error Alert" tab and remove the check mark to the left of "Show error alert after invalid data is entered." This will allow you to enter data that is not in the dynamic list.

 

[Peter_SSs]

When setting up the data validation list, click the "Error Alert" tab and remove the check mark to the left of "Show error alert after invalid data is entered." This will allow you to enter data that is not in the dynamic list.

Problem is .. that would allow duplicate entries which is exactly what that DV was trying to prevent.

If you want to keep the convenience of the drop-down list, allow typed values and avoid duplicates then you may be able to combine @mumps idea with a vba Worksheet_Change event code that checks for (& disallows) duplicates. Would that be a viable option for you? (Noting of course that if the user does not have macros enabled then that system will fail too)

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Peter_SSs]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

The link provided by the OP in post 1 has an XL2BB sample and they have described in post 1 what they are trying to do: type a valid entry from the diminishing DV list.

@Sepkong
If you are interested in the mixed DV/vba method suggested above, try this.

1. Set up the data validation as shown in the linked thread but make the change suggested by @mumps in post #2:

2. Right click the sheet name tab and choose "View Code".
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window & test.
5. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm) & users will need to have macros enabled.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range, EntryRange As Range, DVFullList As Range
  Dim s As String
  
  Set EntryRange = Range("A1:A5")
  Set DVFullList = Range("D1:D4")
  Set Changed = Intersect(Target, EntryRange)
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If Len(c.Value) > 0 Then
        If Application.CountIf(EntryRange, c.Value) > 1 Or Application.CountIf(DVFullList, c.Value) = 0 Then
          s = s & ", " & c.Address(0, 0)
          c.ClearContents
        End If
      End If
    Next c
    Application.EnableEvents = True
    If Len(s) > 0 Then MsgBox "Invalid entry cleared from: " & Mid(s, 3)
  End If
End Sub

 

[Sepkong]

Peter - with macros enabled this is the best solution ...so far I will use this in other projects, but for now I'm going to make online-shared workbook and about 30 employees are going to use it simultaneously - without macros enabled.

I've quoted your (slightly modified) example to be more specific:

Peaches.xlsm
	A	B	C	D	E	F
1				bananas	avocado	$E$1:$E$3
2	apples			oranges	oranges
3				apples	peaches
4	bananas			peaches
5
6
DV without duplicates

Cell Formulas
Range	Formula
F1	F1	=ADDRESS(ROW(E1),COLUMN(E1))&":"&ADDRESS(ROW(E1)+COUNTIF(E1:E5,"?*")-1,COLUMN(E1))
E2:E5	E2	=IFERROR(INDEX(D$1:D$4,AGGREGATE(15,6,(ROW(D$1:D$4)-ROW(D$1)+1)/ISNA(MATCH(D$1:D$4,A$1:A$5,0)),ROWS(E$1:E1))),"")

Cells with Data Validation
Cell	Allow	Criteria
A1:A5	List	=INDIRECT($F$1)

Let's say I want "peaches" in cell A1:
-I can choose "peaches" from the drop-down list,
-I can type "peaches" and mouse click on for example B2,
-I cannot type "peaches" and press Enter, because of error message: "This value doesn't match the data validation restrictions (...)".

In case I want "avocado" in cell A1 - it's no problem, I can type and press Enter, because it is not disappearing from the choice list ("avocado" is not defined by formula making it disappear).

At the moment of typing "p-e-a-c (...)" the value "peaches" is available. Pressing Enter is forcing excel to compute formulas and "peaches" is becoming a Schroedinger's cat It's available and unavailable at the same time.

Any ideas?

 

[Peter_SSs]

Let's say I want "peaches" in cell A1:
-I can choose "peaches" from the drop-down list,
-I can type "peaches" and mouse click on for example B2,
-I cannot type "peaches" and press Enter, because of error message: "This value doesn't match the data validation restrictions (...)".

In case I want "avocado" in cell A1 - it's no problem, I can type and press Enter, because it is not disappearing from the choice list ("avocado" is not defined by formula making it disappear).

At the moment of typing "p-e-a-c (...)" the value "peaches" is available. Pressing Enter is forcing excel to compute formulas and "peaches" is becoming a Schroedinger's cat It's available and unavailable at the same time.

Any ideas?

This seems to be exactly the same question you asked originally. I shared the idea that I had.

It does not change the 'typing' problem but with 365 the DV list setup can be a bit simpler

Sepkong.xlsm
	A	B	C	D	E
1				bananas	oranges
2	apples			oranges	peaches
3				apples
4	bananas			peaches
5
Sheet2

Cell Formulas
Range	Formula
E1:E2	E1	=FILTER(D1:D4,ISNA(MATCH(D1:D4,A1:A5,0)))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A1:A5	List	=$E$1#