Obtaining Unique Number From Data Validation List
 

I have a spreadsheet where I need to obtain a unique number from a
predetermined list for different entries on different rows (these rows are
not grouped together or in sequence). I need to pick this unique number
sequentially however I cannot use the same number twice. I have tried using
a data validation list however this allows the same number to be used
multiple times. Is there a method where the number can only be used once?
--
Regards
Matt

Obtaining Unique Number From Data Validation List
 

Create the Validation List on one sheet, but use a formula that watches
the Validation Cells on the other sheet and hides the numbers as they
become chosen, so they no longer appear in the validation list.

Attached is a sheet showing this scenario.


+-------------------------------------------------------------------+
|Filename: ReducingValidationList.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=59|
+-------------------------------------------------------------------+

--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46886

Obtaining Unique Number From Data Validation List
 

There are instructions here for hiding used items in a data validation list:

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

Matt wrote:
I have a spreadsheet where I need to obtain a unique number from a
predetermined list for different entries on different rows (these rows are
not grouped together or in sequence). I need to pick this unique number
sequentially however I cannot use the same number twice. I have tried using
a data validation list however this allows the same number to be used
multiple times. Is there a method where the number can only be used once?


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Obtaining Unique Number From Data Validation List
 

Debra Dalgleish;169381 Wrote:
There are instructions here for hiding used items in a data validation
list:

'Excel -- Data Validation -- Hide Previous Selections'
(http://www.contextures.com/xlDataVal03.html)

No, that page shows usage of Dependent Lists, lists that completely
change to other lists based on prior choices...sequential List Boxes, if
you will.

The workbook I posted shows a way to ACTUALLY cause individual items to
drop out of the Validation List. I'm still looking for a way to cause a
validation list to skip the blank spots my technique creates in the
list.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46886

Obtaining Unique Number From Data Validation List
 

No, it actually doesn't...

Did you try it?

In article ,
JBeaucaire wrote:

No, that page shows usage of Dependent Lists, lists that completely
change to other lists based on prior choices...sequential List Boxes, if
you will.

Obtaining Unique Number From Data Validation List
 

JE McGimpsey;169390 Wrote:
No, it actually doesn't...

Did you try it?
My apologies. I've used the Contextures page on dependent lists so many
times I just saw that when I brought up the page.

This is a way to do exactly what the OP is looking for, a more elegant
version of what i suggested above, much more elegant. I'd use this.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46886

Obtaining Unique Number From Data Validation List
 

You can save a few keystrokes in the dynamic range formula by replacing:

COUNTA('Employees - Original Formula'!$C$1:$C$6)-COUNTBLANK('Employees -
Original Formula'!$C$1:$C$6)

With:

COUNTIF('Employees - Original Formula'!$C$1:$C$6,"?*")


--
Biff
Microsoft Excel MVP


"Debra Dalgleish" wrote in message
...
There are instructions here for hiding used items in a data validation
list:

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

Matt wrote:
I have a spreadsheet where I need to obtain a unique number from a
predetermined list for different entries on different rows (these rows
are not grouped together or in sequence). I need to pick this unique
number sequentially however I cannot use the same number twice. I have
tried using a data validation list however this allows the same number to
be used multiple times. Is there a method where the number can only be
used once?


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Obtaining Unique Number From Data Validation List
 

Thank you Debra. A bit complicated (for me) but works a treat!
--
Regards
Matt


"Debra Dalgleish" wrote:

There are instructions here for hiding used items in a data validation list:

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

Matt wrote:
I have a spreadsheet where I need to obtain a unique number from a
predetermined list for different entries on different rows (these rows are
not grouped together or in sequence). I need to pick this unique number
sequentially however I cannot use the same number twice. I have tried using
a data validation list however this allows the same number to be used
multiple times. Is there a method where the number can only be used once?


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Obtaining Unique Number From Data Validation List
 

Even better:

You can replace:

COUNTA('Employees - Original Formula'!$C$1:$C$6)-COUNTBLANK('Employees -
Original Formula'!$C$1:$C$6)

With:

COUNT('Employees - Original Formula'!$B$1:$B$6)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
You can save a few keystrokes in the dynamic range formula by replacing:

COUNTA('Employees - Original Formula'!$C$1:$C$6)-COUNTBLANK('Employees -
Original Formula'!$C$1:$C$6)

With:

COUNTIF('Employees - Original Formula'!$C$1:$C$6,"?*")


--
Biff
Microsoft Excel MVP


"Debra Dalgleish" wrote in message
...
There are instructions here for hiding used items in a data validation
list:

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

Matt wrote:
I have a spreadsheet where I need to obtain a unique number from a
predetermined list for different entries on different rows (these rows
are not grouped together or in sequence). I need to pick this unique
number sequentially however I cannot use the same number twice. I have
tried using a data validation list however this allows the same number
to be used multiple times. Is there a method where the number can only
be used once?


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com