only unique Values in a Column

[ferrisj]

So I am using a Validation drop box to sect data in a column. The information is a list defined on another worksheet. I would like to make it so that once I select a value from the list, I can no longer select that value any more (specific to the worksheet, Many different worksheets are pulling off of the same list.)

 

[SkipVought]

Hi,

You can't do 2 differnt validations -- in this case either List or Custom.

Here's the custom formula to exclude duplicates...
[tt]
=SUMPRODUCT(($A$2:A2=A2)*(1))=1
[/tt]
assuming that your list starts in A2.

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[byundt]

ferrisj,
Here is a macro that will update the source for the data validations on the fly. As you choose items from the list, they disappear from the data validations in the other cells. Install this sub in the code pane for the worksheet being watched.

As posted, the macro assumes your data validations are in A1:A10, and the source is a named range called DataValSource. The code is marked so you can change these easily. Two options are shown for specifying the location of the data validations shown; pick the one you like and put a single quote in front of the other.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, cel1 As Range, rgVals As Range, dvSource As Range
Dim s As String
Set rgVals = [A1:A10]       'The data validation dropdowns are in A1:A10
'Set rgVals = Union([A1],[A3],[A5:A8])       'Alternate way to specify location of validation dropdowns 
If Intersect(Target, rgVals) Is Nothing Then Exit Sub

'Build validation source string
Set dvSource = Range("DataValSource")       'The source of data validation is a named range DataValSource
For Each cel In dvSource.Cells
    If cel <> "" Then
        Set cel1 = Nothing
        Set cel1 = rgVals.Find(cel)
        If cel1 Is Nothing Then s = s & "," & cel   'Add items to source string only if not already selected
    End If
Next
If s <> "" Then s = Mid$(s, 2)
If s = "" Then s = " "      'If all values have been used, then the "source" will be a single space

'Update the validation source for each cell in rgVals
For Each cel In rgVals.Cells
    cel.Validation.Delete
    cel.Validation.Add xlValidateList, , , cel & "," & s
Next
End Sub

To install a sub in the code pane for a worksheet:
1) Right-click the sheet tab for the worksheet
2) Choose View Code from the resulting pop-up
3) Paste the suggested code in the resulting module sheet
4) ALT + F11 to return to the worksheet

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

Brad

 

[GlennUK]

You are going to need a list for each worksheet, if you want the value chosen to not appear on the drop-downs specifically for the sheet being used.

I would use a set of calculation columns per sheet name, in the worksheet where the original list is defined, and have formulae update what is available depending on what has already been chosen. Or you could use a macro to do this ( see byundt's post ( above ).

If you want to go this route, post back and let me know, and I'll create some formulae to do this.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.