In cell Drop Down List

[vijay.vizzu]

Dear All,


I am creating a PO Template, i am getting unique values in a drop down named "combo" by using SQL statement in my template. It works fine and meets my requirement. Now i just want to create a list which is to be used in Data Validation to create a incell drop down list. My requirement is, whenever the user selects the value in Drop down box the related items should be fetch from another sheet named as Pendency in data validatino list, so that the user can selected related items on that.


The structure in Sheet name Pendency is as follows;

S.No. - Request No. - Item No - Item name

1 - PUR-001-13 - 12345 - AAA

2 - PUR-001-13 - 67891 - BBB

3 - PUR-001-13 - 25849 - CCC

4 - PUR-002-13 - 15975 - DDD

5 - PUR-002-13 - 85469 - EEE


The values which i am getting in drop down box is in col Request no as

PUR-001-13

PUR-002-13


if the user selects the value PUR-001-13 then

three items list should be appear in data validation list


I can't upload the file due to restriction in my office


Please help

 

[Faseeh]

Assuming your data in A1:D6

[pre]

S.No	Request No	item	Name
1	PUR-001-13	12345	AAA
2	PUR-001-13	67891	BBB
3	PUR-001-13	25849	CCC
4	PUR-002-13	15975	DDD
5	PUR-002-13	85469	EEE[/pre]

Add a helper column in E with this array formula and drag down:


=IFERROR(INDEX($C$2:$C$6,SMALL(IF($B$2:$B$6=$H$2,ROW($B$2:$B$6)-1),ROW(A1)),0),NA())

..where H2 Refers to PUR-001-13 and Define a named range "Select_Item_List" with following:


=OFFSET(Sheet1!$E$2,0,0,COUNT(Sheet1!$E$2:$E$6),1)


..and place this named range in List in Data Validation.


Regards,

 

[vijay.vizzu]

thank you faseeh, it works fine, but can you please tell how to get the value which is selected in Drop down box,

 

[Faseeh]

Hi Vijay,


You said you have two filters, one retrives unique values through an sql query and the second you asked to work for?? Is that correct??