Data validation list - the order of computing formulas

Sepkong

New Member
Joined
Apr 1, 2022
Messages
2
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Web
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?
 
Last edited by a moderator:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.
 
Upvote 0
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)
 
Upvote 0
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).
 
Upvote 0
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:
1649036831510.png

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.

VBA Code:
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
 
Upvote 0
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
ABCDEF
1bananasavocado$E$1:$E$3
2applesorangesoranges
3applespeaches
4bananaspeaches 
5 
6
DV without duplicates
Cell Formulas
RangeFormula
F1F1=ADDRESS(ROW(E1),COLUMN(E1))&":"&ADDRESS(ROW(E1)+COUNTIF(E1:E5,"?*")-1,COLUMN(E1))
E2:E5E2=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
CellAllowCriteria
A1:A5List=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?
 
Upvote 0
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
ABCDE
1bananasoranges
2applesorangespeaches
3apples
4bananaspeaches
5
Sheet2
Cell Formulas
RangeFormula
E1:E2E1=FILTER(D1:D4,ISNA(MATCH(D1:D4,A1:A5,0)))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
A1:A5List=$E$1#
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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
Back
Top