Data validation - complex listbox

[AltaEgo]

Hi

I have a need for data validation in which list entries are reduce when used
anywhere in a pair of rows. However, each entry in the list should remain
useable in the following pair of rows until used there, etc, etc.

Example

List
Cat
Cow
Dog
Bat


R1 Cat Cow
R2 Dog Bat
R3 Cat Dog
R4 Cow
R5
R6 Cat
R7
R8

Cat would only be available in rows 7&8
Cat, Cow, Dog, Bat would not be available in Rows 1 &2
Bat would be available in rows 3-8
Cow, Dog, Bat would be available in rows 5&6
All would be available in rows 7&8

Is someone able to wind me up and point me in the right direction?

 

[RagDyer]

See if this starts you in the right direction:

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

 

[AltaEgo]

Thank you...I think

Hmmm, if I transpose the dynamic list formula; don't anchor the defined name
to a set row; space my pairs of rows with a blank in between and repeat the
dynamic list every third row... Fat, slow workbook?

OK, not as elegant as the original concept but how about letting the user
sort out what has or has not been used with some scratching of the head
(list sorted in alphabetical order) and use conditional formatting that
highlights duplicates '=COUNTIF($D$2:$N$3,D2)>1'.

Revised question, How do I turn the above idea for conditional formatting
into something like the following so it actually works when copied to other
cells?

=COUNTIF($D$ & row() & :$N$ & row()+1 & ,D2)>1

 

[AltaEgo]

OTOH

Calling the following from Worksheet Change together with a few other pieces
of code to resort the list to be continuous and not move the cursor of the
user enters a name manually is working a treat.


Sub RemoveFromList()
Dim valToRemove
valToRemove = Selection.Value

Range("a:A").Replace What:=valToRemove, _
Replacement:="", LookAt:=xlPart, MatchCase:=False

End Sub

All that is left is to write some code to rebuild the lookup list; make some
of the range references dynamic and the task is finalised.

 

[Debra Dalgleish]

There's a sample file here with data validation that hides selections
made in previous columns:

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

Under Data Validation, look for DV0016 - Assign Employees to Single Task
Per Day

 

[AltaEgo]

OTOOH

Deborah's method easily handles deleted entered values. Looks like its back
to the think tank on that method before committing too deeply on one method
or another.

BTW if anyone is following the thread, the Sub below was bashed together in
haste as a test.

xlWhole rather than xlPart is highly recommended.
MatchCase:=True would also be an improvement.