Preventing duplicate entries using a data validation list

**turbofatty** · 04-21-2010, 08:38 AM

Hi there,

I was wondering if one of you genius' out there could help me?

I have a sheet (see attached), where i want to input wagon numbers on sheet 1. (There is a named range on sheet 2 called wagonnumbers, used for data validation)

What i don't want is the users of this sheet to input a duplicate wagon number twice in the column.

I can put on normal data validation list, but that doesn't prevent duplicated entries.

I also figured out that i can prevent duplicate entries of wagons using data validation custom and typing in a Countif if formula, but that doesn't use the list drop downs that i desire.

I am looking at making the sheet as simple to use for the end user and don't really want to go down the VB macro route if possible.

In a nutshell, i'm after a data validation list that prevents duplicate entries, CAN THIS BE DONE???

Cheers Guys

**Ron Coderre** · 04-21-2010, 08:59 AM

This isn't perfect...but it does a fairly good job. (see the attached workbook)

Here's what I did:
• Put a series of formulas, next to the wagon numbers list, that lists unused wagon numbers.
• Re-defined wagonnumbers as a dynamic range name that refers to those formulas.

Now, the input area only shows available wagon numbers.

Is that something you can work with?

**turbofatty** · 04-21-2010, 09:19 AM

Hi Ron,

Thats looks very clever!!

Couple of things.......

You say that the dynamic rang is defined as: =OFFSET(Sheet2!$B$2,,,COUNT(Sheet2!$B$2:$B$21),1)

What does that mean (dynamic range)?????? I can't see anywhere on the sheet where thats entered??

Also, it is still letting me enter duplicate wagons the further i go down column A on sheet 1?? (ive probably bust it!!)

It would be good, i just need a bit more understanding of the sheet because i will be asked to support it if it goes wrong!!

Cheers.

**Ron Coderre** · 04-21-2010, 09:57 AM

Ooops! My mistake.

The array formula on Sheet2, cell B2, should be this:

=SMALL(IF(COUNTIF(Sheet1!$A$1:$A$21,$A$2:$A$21)=0,$A$2:$A$21),ROWS(B$2:B2))

Commit that formula with CTRL+SHIFT+ENTER, instead of just ENTER.
Copy B2 into B3 and down through B21

The dynamic range name wagonnumbers automatically resizes to only allow unused numbers by using this formula as its reference:

=OFFSET(Sheet2!$B$2,,,COUNT(Sheet2!$B$2:$B$21),1)

Debra Dalgleish covers dynamic range names at her website:
http://www.contextures.com/xlNames01.html#Dynamic

I hope that helps

**turbofatty** · 04-21-2010, 10:12 AM

Cheers for this Ron.

I forgot the CTRL-SHIFT-ENTER to enter the array formula, DOH!!

I think it does the job, couple that with warning messages in data validation and i don't think they can go too far wrong!!

Many Thanks for your help