is there any way to make data validation doesn't contain empty or duplicates items

[Mussala]

Hi guys

I have three data validations in OUTPUT sheet in columns B,C,D , linked with columns B,C,D for INVOICE sheet like this

=INVOICE !$B$2:$B$100,=INVOICE !$C$2:$C$100,=INVOICE !$D$2:$D$100

what I look for when shows any data validation should not show any empty and any duplicates items , also I want populate the items into data validation dynamically without increase the range manually when I need add more items more than row 100 for each column .

thanks

 

[arthurbr]

Are you still on XL2019?

 

[Mussala]

yes

 

[Akuini]

If you're willing to use an add-in, you can try a free Excel add-in called "Search deList". You'll get a unique, sorted & non-blank data validation list & and it's also searchable.
You can find it here:

I created an Excel add-in called “Search deList”, to create searchable data validation

I created an add-in called “Search deList”, and I share it as a freeware. Its function is to speed up searching in data validation list. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox. You can type multiple keywords (separated by a...

www.mrexcel.com

 

[Mussala]

@Akuini
thank you ,but this step

1. In any cell that has data validation, pressing ALT+RIGHT will open a Userform with a combobox. doesn't work for me , I'm not sure if my laptop needs different keys or there is mistake I make it !

 

[Akuini]

@Akuini
thank you ,but this step

1. In any cell that has data validation, pressing ALT+RIGHT will open a Userform with a combobox. doesn't work for me , I'm not sure if my laptop needs different keys or there is mistake I make it !

Make sure the file is not blocked.
Try this:

Please check this article on how to deal with this problem:

Microsoft Excel — HOW TO Disable “Protected View” in MS Excel

Overview

medium.com

OR

In windows explorer: Right-click the file, and select Properties from the menu. On the General tab of the file properties dialog, place a check mark for the Unblock option.

 

[Akuini]

Make sure the file is not blocked.

By the file I meant the add-in file.

 

[Mussala]

thanks for the article and I follow all of the steps and made sure is added, but unfortunately doesn't show anything !

 

[Akuini]

thanks for the article and I follow all of the steps and made sure is added, but unfortunately doesn't show anything !

A user reported similar problem, and I still don't know what caused it.
Could you try activating the combobox by double-clicking the cell?

Here’s how:
Copy-paste this code into Thisworkbook code window of your workbook:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If TypeName(Selection) = "Range" Then
    Dim v
    On Error Resume Next
        v = ActiveCell.Validation.Type
    On Error GoTo 0
    'if activecell has data validation type 3
    'run "Search deList" add-in by doubke-clicking a cell
    If v = 3 Then Cancel = True: Application.Run ("toShow__dheeDAV")
End If
End Sub

Save, close then reopen your workbook.
Now try double-clicking any cell that has data validation (with List type), it should open the combobox.
If it does open the combobox then the problem probably about the keyboard shortcut which is "%{RIGHT}" or Alt+Right.

 

[Mussala]

it gives message