Printable View
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.
There were references in the columns E and F.
This version have had these references changed to values.
The names are uniue, they don't have spaces, so what is the problem?
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!
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.
The above method comes care of http://www.exceltip.com/st/Preventin..._Data/886.html
Down further there's a section of code posted by "john" which goes as follows;
[VBA]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[/VBA]
There is a direct DV method shown here See http://www.contextures.com/xlDataVal03.html
Aussiebear
I used the datavalidation method shown in your first reply.
Only i changed it to range
It lokk like this then:
[VBA]=COUNTIF($A$4:$A$41;A4)=1[/VBA]