PDA

View Full Version : Created a Drop Down Box based on certain criteria!



brennaboy
03-25-2011, 10:14 AM
Hi All,

I need help and guidance...I hope I explain this properly...

I have two columns of data, in column 1 I have account numbers and in column two I have some items. There is no unique key.

I want to make a cell a drop down box that will be dependant on what is contained in another cell.

So in Cell 1, you enter an account number and then in Cell 2 a drop down box will be created with options based on the following:

Find all entries in column 1 that match what is entered in Cell 1 and what ever is next to it in column 2, add as an entry in the drop down box.

Example of data:

Account Asset Number
001 A
002 B
003 C
003 D
003 E
004 F
004 G
005 H

So if you entered 003 in Cell 1, Cell 2 would become a drop down list with C, D and E in it. But if you entered 004 in Cell 1, then F and G would be in the drop down box in Cell 2

Can anyone help me with this?

Many thanks,

Bren.

mikerickson
03-25-2011, 10:53 AM
If Cell1 is C1, and the entries in column A are sorted ascending (as in the example) then you can define a name
Name: myRange
RefersTo: =INDEX($B:$B, MATCH($C$1, $A:$A, 0), 1):INDEX($B:$B, MATCH($C$1, $A:$A), 1)

and then put List style validation on Cell2 with the list source =myRange