Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i create a short cut on my desktop for a excell file? | Excel Discussion (Misc queries) | |||
How do i Use Excell to create a stem and leaf diargram? | Excel Discussion (Misc queries) | |||
How to create Multiple Conditional Formulas in a single cell? | Excel Discussion (Misc queries) | |||
How do I create a daily spending spreadsheet in Excell? | Excel Worksheet Functions | |||
how do I create a drop down menu in Excell? | Excel Worksheet Functions |