General discussion

Locked

Excel 97 - Preventing a Duplicate Entry

By holstonglass ·
Is there a formula I can enter that would prevent the same number from being entered more than once into a column of numbers?

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel 97 - Preventing a Duplicate Entry

by sunny@home In reply to Excel 97 - Preventing a D ...

Hi,

one solution is to have an entry field on the left side and an formula in the right filed, that gets the information of the left, then serach through an database (would be the left column) to verify. It's very simple and looks terrible.

Another possibility is to write an vba program with change-event to verify the entries. The vba program gets the cell-address (activecell-property) where you entered a value and searches the rest of the defined row or column. If there is a match with the same value the programm should give you an hin and writes into the depart-cell an empty value.

See the online-help in the vba-editor for more info.

Collapse -

Excel 97 - Preventing a Duplicate Entry

by holstonglass In reply to Excel 97 - Preventing a D ...

The question was auto-closed by TechRepublic

Collapse -

Excel 97 - Preventing a Duplicate Entry

by Kanny In reply to Excel 97 - Preventing a D ...

It can be solved by using the Data Validation feature. I do realise that it works. Please try yourself, or I will post the solution later.

Collapse -

Excel 97 - Preventing a Duplicate Entry

by holstonglass In reply to Excel 97 - Preventing a D ...

The question was auto-closed by TechRepublic

Collapse -

Excel 97 - Preventing a Duplicate Entry

by Kanny In reply to Excel 97 - Preventing a D ...

Amendment!!!!
Sorry for the misleading. The right way for the answer#2 is using the Conditional Formatting instead of Data Validation. I'm sure it will work.

Collapse -

Excel 97 - Preventing a Duplicate Entry

by holstonglass In reply to Excel 97 - Preventing a D ...

The question was auto-closed by TechRepublic

Collapse -

Excel 97 - Preventing a Duplicate Entry

by henry.ongtauco In reply to Excel 97 - Preventing a D ...

This small code would only work if you enter numbers in the column in descending fashion, that is, the current entry is the lowest.

Write this in the worksheet_change event as follow:

Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim lCur as long, lRow as long
Const iNoDup as integer = 1
lCur = Target.Row
For lRow = 1 to lCur - 1
If ActiveSheet.Cells(lRow, iNoDup) = Target Then
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Exit For
End If
Next
End Sub

Collapse -

Excel 97 - Preventing a Duplicate Entry

by holstonglass In reply to Excel 97 - Preventing a D ...

The question was auto-closed by TechRepublic

Collapse -

Excel 97 - Preventing a Duplicate Entry

by holstonglass In reply to Excel 97 - Preventing a D ...

This question was auto closed due to inactivity

Back to Windows Forum
9 total posts (Page 1 of 1)  

Operating Systems Forums