PDA

View Full Version : Solved: Only unique items



perhol
01-20-2008, 02:20 PM
In a worksheet (Ansatte) i am having a column with names in the range A4:A41, i named the range "Navne".

These names are copyed to 8 other sheets.
On these sheets code is looking up corresponding values in the sheet (Ansatte) in columns D, E and F and showing the name and these values in the statusbar.

In order to have the lookup working correct the names must be unique and contain no space.

I attach the sheet in question.

perhol
01-20-2008, 02:27 PM
There were references in the columns E and F.
This version have had these references changed to values.

Bob Phillips
01-20-2008, 03:46 PM
The names are uniue, they don't have spaces, so what is the problem?

perhol
01-20-2008, 03:54 PM
Names may change, they are employes and employes may quit ore get sacked.
I am not the user of the sheet, and those who are using it could allow anything in the list.
I would like to ensure that only unique names without spaces can be entered!

Aussiebear
01-20-2008, 04:49 PM
G'day perhol,

You could use Data Validation as follows:
1. Select the range to validate (Range A:A)
2. Data --> Data Validation --> Settings
3. From the Allow dropdown list, select Custom.
4. Enter the following COUNTIF formula in the Formula box:
=COUNTIF($A:$A,A2)=1
5. Select the Error Alert tab.
6. In the Title box, enter "Duplicate Entry".
7. In the error box, enter "The value you entered already appears in the list above."
8. Click OK.

After following this procedure, any duplicate entry will cause the error message to pop up, forcing the user to enter a different value.

Aussiebear
01-20-2008, 04:51 PM
The above method comes care of http://www.exceltip.com/st/Preventing_Duplicates_When_Entering_Data/886.html

Aussiebear
01-20-2008, 04:53 PM
Down further there's a section of code posted by "john" which goes as follows;

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If IsEmpty(Target.Value) Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Column < 3 Then Exit Sub
With Range(Cells(1, Target.Column).Address & ":" & Cells(Target.Row - 1, Target.Column).Address & "," & Cells(Target.Row + 1, Target.Column).Address & ":" & Cells(Rows.Count, Target.Column).Address)
Set c = .Find(Target.Value, , , xlWhole)
If Not c Is Nothing Then
MsgBox "Preference already exists at range: " & c.Address(0, 0)
Target.Value = ""
End If
End With
End Sub

Bob Phillips
01-20-2008, 05:54 PM
There is a direct DV method shown here See http://www.contextures.com/xlDataVal03.html

perhol
01-20-2008, 07:22 PM
Aussiebear
I used the datavalidation method shown in your first reply.
Only i changed it to range
It lokk like this then:

=COUNTIF($A$4:$A$41;A4)=1