Unique numbers from data validation list

[nick_thomson]

Hi

I have a spreadsheet that has multiple worksheets each containing list
of items at a warehouse. Each item needs to have a unique number.

When adding items to this spreadsheet I would like, if possible, t
have a function that allows me to choose a unique number from a dro
down data validation list, such that if I were to choose a particula
number from this list in one cell, then that number would be remove
from the list for any subsequent cells. Also, if an item is remove
from any of the worksheets, that item's number should again b
available in the list.

Is this possible with a function?

Many thanks in advance.

Nick Thomso

 

[Bob Phillips]

See http://www.contextures.com/xlDataVal03.html

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"nick_thomson" <[email protected]>
wrote in message
news:[email protected]...

 

[Guest]

Here's a macro solution. Type your list of numbers into a sheet, select the
list and name the range "inputrange". Highlight the column into which you
want to choose numbers and choose Data, Validation. Choose "List" in the
Allow box and type "=inputrange" as Source and click OK. Then go into the VB
Editor (Alt-F11), double-click on the correct sheet module on the left and
then paste the following into the module window on the right:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myFound As Boolean
If Target.Column = 1 Then
If Target.Cells.Count = 1 Then
If Not IsEmpty(Target) Then
If
ActiveWorkbook.Names("inputrange").RefersToRange.Find(Target.Value).NumberFormat = ";;;" Then
Target.Value = ""
Exit Sub
End If
End If
End If
myFound = False
For Each myCell In ActiveWorkbook.Names("inputrange").RefersToRange
If Not Columns("A:A").Find(myCell.Value) Is Nothing Then myFound
= True
If myFound Then
myCell.NumberFormat = ";;;"
Else
myCell.NumberFormat = "General"
End If
myFound = False
Next
End If
End Sub

 

[nick_thomson]

Thanks Bob and Martin for your replies.

Bob's solution may be too complicated as my workbook has many sheets
so I tried Martin's macro solution first.

I followed your instructions Martin, but I get a compile syntax erro
on the red 'if' below:

If Not IsEmpty(Target) Then
If
ActiveWorkbook.Names("inputrange").RefersToRange.
ind(Target.Value).NumberFormat = ";;;" Then
Target.Value = ""

I'm pretty hopeless with VB - any ideas?

Cheers

Nic

 

[Guest]

That's just the strange little text editing package M/soft give us here - the
line's word wrapped when it shouldn't! I've tried to correct it below but no
doubt more hard returns will creep in. The "If" up to the "Then" after it
should all be on one row.

Having said that, Bob's solution is better because you don't see gaps in the
dropdown list unlike my rather unorthodox method!

Thanks Bob and Martin for your replies.

Bob's solution may be too complicated as my workbook has many sheets,
so I tried Martin's macro solution first.

I followed your instructions Martin, but I get a compile syntax error
on the red 'if' below:

If Not IsEmpty(Target) Then
If
ActiveWorkbook.Names("inputrange").RefersToRange.Find(Target.Value).NumberFormat = ";;;" Then
Target.Value = ""