Obtaining Unique Number From Data Validation List

[Matt]

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?

 

[JBeaucaire]

Create the Validation List on one sheet, but use a formula that watche
the Validation Cells on the other sheet and hides the numbers as the
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
+-------------------------------------------------------------------

 

[Debra Dalgleish]

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

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

 

[JBeaucaire]

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

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

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

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

 

[JE McGimpsey]

No, it actually doesn't...

Did you try it?

 

[JBeaucaire]

No, it actually doesn't..

Did you try it

My apologies. I've used the Contextures page on dependent lists so man
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 elegan
version of what i suggested above, much more elegant. I'd use this

 

[T. Valko]

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,"?*")

 

[Matt]

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

 

[T. Valko]

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)