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?

--
Steve

See if this starts you in the right direction:

http://www.contextures.com/xlDataVal03.html
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"AltaEgo" <Somewhere@NotHere> wrote in message
news:OhFOXYXuIHA.524@TK2MSFTNGP05.phx.gbl...
> 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?
>
> --
> Steve

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

--
Steve

"RagDyer" <ragdyer@cutoutmsn.com> wrote in message
news:#o1u3AeuIHA.672@TK2MSFTNGP02.phx.gbl...
> See if this starts you in the right direction:
>
> http://www.contextures.com/xlDataVal03.html
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "AltaEgo" <Somewhere@NotHere> wrote in message
> news:OhFOXYXuIHA.524@TK2MSFTNGP05.phx.gbl...
>> 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?
>>
>> --
>> Steve
>
>

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.

--
Steve





"AltaEgo" <Somewhere@NotHere> wrote in message
news:uvNnEoguIHA.1504@TK2MSFTNGP05.phx.gbl...
> 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
>
> --
> Steve
>
> "RagDyer" <ragdyer@cutoutmsn.com> wrote in message
> news:#o1u3AeuIHA.672@TK2MSFTNGP02.phx.gbl...
>> See if this starts you in the right direction:
>>
>> http://www.contextures.com/xlDataVal03.html
>> --
>> HTH,
>>
>> RD
>>
>> ---------------------------------------------------------------------------
>> Please keep all correspondence within the NewsGroup, so all may benefit !
>> ---------------------------------------------------------------------------
>>
>> "AltaEgo" <Somewhere@NotHere> wrote in message
>> news:OhFOXYXuIHA.524@TK2MSFTNGP05.phx.gbl...
>>> 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?
>>>
>>> --
>>> Steve
>>
>>

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 wrote:
> 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?
>


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

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.

--
Steve

"AltaEgo" <Somewhere@NotHere> wrote in message
news:e9dnbjhuIHA.3792@TK2MSFTNGP02.phx.gbl...
> 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.
>
> --
> Steve
>
>
>
>
>
> "AltaEgo" <Somewhere@NotHere> wrote in message
> news:uvNnEoguIHA.1504@TK2MSFTNGP05.phx.gbl...
>> 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
>>
>> --
>> Steve
>>
>> "RagDyer" <ragdyer@cutoutmsn.com> wrote in message
>> news:#o1u3AeuIHA.672@TK2MSFTNGP02.phx.gbl...
>>> See if this starts you in the right direction:
>>>
>>> http://www.contextures.com/xlDataVal03.html
>>> --
>>> HTH,
>>>
>>> RD
>>>
>>> ---------------------------------------------------------------------------
>>> Please keep all correspondence within the NewsGroup, so all may benefit
>>> !
>>> ---------------------------------------------------------------------------
>>>
>>> "AltaEgo" <Somewhere@NotHere> wrote in message
>>> news:OhFOXYXuIHA.524@TK2MSFTNGP05.phx.gbl...
>>>> 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?
>>>>
>>>> --
>>>> Steve
>>>
>>>