Validating Drop Down Lists to not allow duplicates 1

[alwayslrN]

I want to create a sheet that will have about 8 colums. Two of the columns will be drop down lists. The combination of the values chosen from the list cannot be duplicated. I was wondering how to code for that?

Say for example the user selected on row 2
Nissan Maxima

and on row 5

Nissan Maxima again.

I do not want to allow them to do that. Would I have to do some type of loop or something. Also, what would be the best way to populate the drop down lists, using Data Validation | List or through VBA code?

 

[JA3395]

Are the two lists going to be identical?


apple apple
orange orange
pear pear
banana banana
grape grape

And you just want to generate the second list from the selection on the first list?


apple orange
pear
banana
grape

 

[GlennUK]

Are these 2 lists connected in some way? For example, does the contents of the second list depend on the choice made in the first list?

Is it the combination of item from list 1, with choice of item from list 2 that you want to be unique? If so, have you thought of having a list of allowable combinations for a drop-down, and have that list controlled by formulae to eliminate possibilities as they are chosen?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[alwayslrN]

Yes, the two lists are connected in some way.

In a worksheet I have a table that looks something like the following.

Pool Loan
3711111 8558866
3711111 8755566
3711111 8896567
3712222 7566447
3712222 7566565

In a different worksheet I need to create drop down lists to hold these two sets of corresponding information, but I want the prebvent the user from selecting duplicate cominations AND the cominations have to be as listed above. I cannot have them pick 3711111 and 7566565, for example.

I have not considered much of anything, unfortunately, because this is way above my skill level.

BTW im using Excel 2002

Thanks

 

[Gavona]

Have a look here:

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

Gavin

 

[JA3395]

Gavin,

That would work if the named ranges at least started with a letter. You can't name ranges as all numeric.

J.

 

[JA3395]

If you added a letter to the front of your "Pool" numbers, e.g.

A3711111
A3712222

Then you could use these as the Range Name. Strip the letter off later if you need to use it.

J.

 

[GlennUK]

Have an extra column for combination:
[tt]
Pool Loan Combo
3711111 8558866 3711111 8558866
3711111 8755566 3711111 8755566
3711111 8896567 3711111 8896567
3712222 7566447 3712222 7566447
3712222 7566565 3712222 7566565
[/tt]
and then have another column after that to check whether the combination has been used:

=NOT(ISNA(MATCH(C2,Sheet2!$A$2:$A$99,0)))

where C2 is the combo to be checked ( "3711111 8558866" above ), and Sheet2!$A$2:$A$99 is the area where the user picks combinations from in-cell Data Validation drop-downs. Copy down as required.

After that have another column for counting how many unused combinations are in the list:

=COUNTIF(D2:$D$2,FALSE)

which looks at the "combo used" column ( note the $ to make the start position of the formula absolute ), copied down as required.

Finally have a list of unused combinations ( used as the source for the in-cell Data Validation drop-downs ):

=INDEX($C$2:$C$999,MATCH(ROW()-1,$E$2:$E$999,0))

this gets combinations from column C where the combination is the next unused one.

To make a Defined Name to refer to the "Unused" list, do menu command Insert/Name and choose a name of Mylist with a definition of:

=OFFSET(Sheet1!$F$2,0,0,MAX(Sheet1!$E:$E),1)

and use this name for the Data Validation List source.



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[alwayslrN]

Thanks everyone - I will have to take a look at these suggestions. As soon as I do I'll keep everyone posted as to my success.

 

[alwayslrN]

Glenn - thank you so much for the very detailed information

Am I correct in thinking that MyList is something that should change dynamically based on what was chosen from the list?

If so I do not think it is working. In the sheet where I have

=INDEX($C$2:$C$999,MATCH(ROW()-1,$E$2:$E$999,0))

The values are change from the combo to N/A, but the drop down list contains all of the combinations.


Thanks again

 

[alwayslrN]

This is what I get when I select 3711111 8755566


Pool    Loan    Combo
3711111 8558866 3711111 8558866 FALSE 1 3711111 8558866
3711111 8755566 3711111 8755566 TRUE 1 3711111 8896567
3711111 8896567 3711111 8896567 FALSE 2 3712222 7566447
3712222 7566447 3712222 7566447 FALSE 3 3712222 7566565
3712222 7566565 3712222 7566565 FALSE 4 #N/A

 

[GlennUK]

It is working. Except you have the unused list in column F but are still using column C for the in-cell drop-down.

I have given the formula to use for MyList, which defined the range for the in-cell drop-down in Data Validation, from column F.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[GlennUK]

Have you got it working now?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[GlennUK]

I wonder sometimes.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[alwayslrN]

Hi Glenn - thanks so much this worked like a charm.

Sorry for making you think I had blown you off. I had to step away from this to implement a new web application for work and then I went on a much needed vacation. This is the first opportunity I have had to get back to this.

Just finished it and works wonderfully.

Thanks again for your help and detail directions - both were very helpful.

All the best

Pam

 

[GlennUK]

Hi Pam, I'm glad it worked so well.

And I hope you enjoyed your vacation too ... am on vacation myself now ( also much needed ).



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.