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.

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

--Select B2:H2
--Select menu DataValidation
--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
---------------
Jacob Skaria


"A523309" wrote:

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.

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

On Fri, 14 Aug 2009 09:01:02 -0700, A523309
wrote:

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

"Jacob Skaria" wrote:

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

--Select B2:H2
--Select menu DataValidation
--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
---------------
Jacob Skaria


"A523309" wrote:

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.