Two validations

**Statsman** · 05-12-2009, 08:17 AM

Hello everyone,

I need your help!

I have 2 columns in a table. I would like to populate each column in the validation list.

Example:
Col1 Col2
A 1
A 2
A 3
B 4
B 5
C 6
C 7
C 8
C 9

I would like to put the 1st column in the 1st validation list without duplicates. If for example, A is selected, the second validation list will only show 1,2,and 3. Is this possible? Could you please provide an sample formula?

Your help on this is appreciated.

Statsman

**NBVC** · 05-12-2009, 08:34 AM

Have a look at this thread using comboboxes and vba.

http://www.excelforum.com/excel-prog...bobox-1-a.html

**Statsman** · 05-12-2009, 09:01 AM

Hi NVBC,

Unfortunately, I do not require the combo boxes as the selections should be populated in each depending on the number of rows. I would like to utilize these validations as selections in another table. Let's pretend that the user has chosen the data from the table I have posted originally.

For example:
Name Validation1 Validation2
George A 1
Brenda B 3
Coreen C 4
Alex A 1

Is this possible?

Thanks,

**zbor** · 05-12-2009, 09:13 AM

Try look again solution that NBVC gave you:

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

**NBVC** · 05-12-2009, 09:42 AM

Originally Posted by Statsman

Hi NVBC,

Unfortunately, I do not require the combo boxes as the selections should be populated in each depending on the number of rows. I would like to utilize these validations as selections in another table. Let's pretend that the user has chosen the data from the table I have posted originally.

For example:
Name Validation1 Validation2
George A 1
Brenda B 3
Coreen C 4
Alex A 1

Is this possible?

Thanks,

Unless you create a unique list of the first column elsewhere and refer to that in your data validation list, then you will see duplicates... then you can continue as per the link provided by zbor.

**contaminated** · 05-12-2009, 04:53 PM

I was thinking for about 2 hours over it and hope it helps.

Here's creted Unique List

Formula for unique List:
=IF(ISERROR(INDEX(A1:A29,SMALL(IF(IF(ISERROR(MATCH(A1:A29,A1:A29,0)),"",MATCH(A1:A29,A1:A29,0))<>ROW(INDIRECT("1:"&ROWS(1:29))),"",IF(ISERROR(MATCH(A1:A29,A1:A29,0)),"",MATCH(A1:A29,A1:A29,0))),ROW(INDIRECT("1:"&ROWS(1:29)))))),"",INDEX(A1:A29,SMALL(IF(IF(ISERROR(MATCH(A1:A29,A1:A29,0)),"",MATCH(A1:A29,A1:A29,0))<>ROW(INDIRECT("1:"&ROWS(1:29))),"",IF(ISERROR(MATCH(A1:A29,A1:A29,0)),"",MATCH(A1:A29,A1:A29,0))),ROW(INDIRECT("1:"&ROWS(1:29))))))

Than Created formula for depending list

=OFFSET($B$1,MATCH($E$2,$A:$A,0),,COUNTIF($A:$A,$E$2))

In short see the book!!!

Hope it help