Prevent duplicate entries within a range of cells from a drop-down

[A523309]

I have a drop-down list that provides input for end-user across a range of
cells. Range is B2:H2 and I want to prevent users from picking duplicate
entries from the drop-down list to populate the range.

Eg.

Drop-down list has 5 entries...A, B, C, D & E
Cell Range is B2:F2
If 1 user chooses A from the drop-down list for cell B2 then the only valid
entries for C2:F2 are B,C,D & E. User # 2 should not be allowed to pick A as
it has already been picked for cell B2.

This is going to be used to help figure out sessions for a technical summit
and ensuring the same session is not picked by multiple attendees which would
cause resourcing conflicts.

 

[Jacob Skaria]

Using data validation you can; but only manual entries/dropdown entries will
be validated

--Select B2:H2
--Select menu Data>Validation
--Select Custom from the 'Allow' dropdown and enter the below formula

=COUNTIF($B$2:$H$2,B$2)=1
--From the Error alert tab enter the message you want to be displayed
--Click OK and try.

If this post helps click Yes

 

[A523309]

Tried that however only manual entries are validated. For me to use the
drop-down list I have to:

--Select Data Validation
--Select 'List' from the dropdown
--then select the range which contains my input list
--cant put the COUNTIF formula on that then

 

[Gord Dibben]

See Debra Dalglesih's site for hiding previously used items in DV dropdown
lists.

http://www.contextures.on.ca/xlDataVal03.html


Gord Dibben MS Excel MVP