Hi

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

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

Is this possible with a function?

Many thanks in advance.

Nick Thomson


--
nick_thomson
------------------------------------------------------------------------
nick_thomson's Profile: http://www.excelforum.com/member.php...o&userid=32929
View this thread: http://www.excelforum.com/showthread...hreadid=527536

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"nick_thomson"
wrote in message
news:nick_thomson.25fk6n_1143634203.0645@excelforu m-nospam.com...

Hi

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

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

Is this possible with a function?

Many thanks in advance.

Nick Thomson


--
nick_thomson
------------------------------------------------------------------------
nick_thomson's Profile:
http://www.excelforum.com/member.php...o&userid=32929
View this thread: http://www.excelforum.com/showthread...hreadid=527536

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.F ind(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" wrote:


Hi

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

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

Is this possible with a function?

Many thanks in advance.

Nick Thomson


--
nick_thomson
------------------------------------------------------------------------
nick_thomson's Profile: http://www.excelforum.com/member.php...o&userid=32929
View this thread: http://www.excelforum.com/showthread...hreadid=527536

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.F
ind(Target.Value).NumberFormat = ";;;" Then
Target.Value = ""

I'm pretty hopeless with VB - any ideas?

Cheers

Nick


--
nick_thomson
------------------------------------------------------------------------
nick_thomson's Profile: http://www.excelforum.com/member.php...o&userid=32929
View this thread: http://www.excelforum.com/showthread...hreadid=527536

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!

"nick_thomson" wrote:


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.F ind(Target.Value).NumberFormat = ";;;" Then
Target.Value = ""

I'm pretty hopeless with VB - any ideas?

Cheers

Nick


--
nick_thomson
------------------------------------------------------------------------
nick_thomson's Profile: http://www.excelforum.com/member.php...o&userid=32929
View this thread: http://www.excelforum.com/showthread...hreadid=527536