How do I create conditional "List of Values" in Excell?
 

I have a set of data with two columns. The data can repeat itself in both
columns. Ex:
A 1
A 2
A 3
B 3
B 4
B 5
I want to have the second column(1,2,3,4,5) as the source for a drop down
list but I want to be able to limit the values by the data on the first
column. Ex. I choose A and the list of possible values should be 1,2 and 3
whereas when I choose B the possible values should be 3, 4 and 5. Your advise
is very much appreciated.

How do I create conditional "List of Values" in Excell?
 

Put A in C1,
Then select D1:D20 and in the formula bar enter this array formula

=IF(ISERROR(SMALL(IF($A$1:$A$20=C1,ROW($A1:$A20)," "),ROW($A1:$A20))),"",
INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20=C1,ROW($A1:$A 20),""),ROW($A1:$A20))))

and commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SANCAKLI" wrote in message
...
I have a set of data with two columns. The data can repeat itself in both
columns. Ex:
A 1
A 2
A 3
B 3
B 4
B 5
I want to have the second column(1,2,3,4,5) as the source for a drop down
list but I want to be able to limit the values by the data on the first
column. Ex. I choose A and the list of possible values should be 1,2 and 3
whereas when I choose B the possible values should be 3, 4 and 5. Your
advise
is very much appreciated.

How do I create conditional "List of Values" in Excell?
 

Dear Bob,
Thank you very much for your quick and useful reply. Using your formula
below I was able to get the relevant data on a seperate column and use that
column as a source for my drop-down list (validation). The problem, however,
is that I have 25 rows to fill and therefore I need to use your fomula 25
times to define 25 different columns as source for each row. Since I am not
able to attach files here I sent you an e-mail to your address
). I hope you got it. If not please let me know
and I will send it to you again. In this e-mail I have attached an excell
table which shows what exactly I am trying to do. Hope you will have a couple
of minutes to help me.

best regards,

"Bob Phillips" wrote:

Put A in C1,
Then select D1:D20 and in the formula bar enter this array formula

=IF(ISERROR(SMALL(IF($A$1:$A$20=C1,ROW($A1:$A20)," "),ROW($A1:$A20))),"",
INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20=C1,ROW($A1:$A 20),""),ROW($A1:$A20))))

and commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SANCAKLI" wrote in message
...
I have a set of data with two columns. The data can repeat itself in both
columns. Ex:
A 1
A 2
A 3
B 3
B 4
B 5
I want to have the second column(1,2,3,4,5) as the source for a drop down
list but I want to be able to limit the values by the data on the first
column. Ex. I choose A and the list of possible values should be 1,2 and 3
whereas when I choose B the possible values should be 3, 4 and 5. Your
advise
is very much appreciated.