I have a drop down list that I want to use to lookup text values in another sheet!

Hello,

I have a "dropdown list" on sheet 1 with names that i want to use as a lookup value. Will attach a picture of the dropdown list.

On sheet 2 I have two colums A and B wich colum A matches the dropdown list with names in sheet 1 and cloum B has a text string.




Example:

A B

AJO - Are Johan M.O057C.6M.A.0009
AJO - Are Johan M.O057C.8M.1.0013
AK - Anders M.O057C.3A.A.0001
AK - Anders M.O057C.4A.A.0052

What I want is to use the dropdown list on sheet 1 and retrurn all values for "AJO" in colum B (sheet 2) in anoter cell beside the dropdown list. To calrify a little more I want for each value found on AJO I want each value to return in separate cells. I hope it can look like this.

A1 ("dropdown list" Are Johan) B1
M.O057C.6M.A.0009
M.O057C.8M.1.0013


Hope some one can help me.

Ok, here is the excel file.

Originally Posted by NBVC

In C2 of Ark1:

=IF(A2='Sheet 1'!$B$1,COUNT(C$1:C1)+1,"")

copied down. This finds matches.

In D2 of Ark1:

=MAX(C:C)

this gives total count

In C2 of Sheet1 (or wherever you want to put list):

=IF(ROWS($A$1:$A1)>Ark1!$D$2,"",INDEX(Ark1!$B:$B,MATCH(ROWS($A$1:$A1),Ark1!$C:$C)))

copied down as far as you want.

Thanks a lot. Thats helped me a lot. Is it possible to get the list dynamic? I have set it up as I want now.

Originally Posted by NBVC

Assuming the names in Ark1 are grouped together..

Create 2 named ranges for the Ark1 sheet.

Names referring to Ark1!$A$2:$A$102

and

Wbs referring to Ark1!$B$2:$B$102 then

change the Data Validation List formula to:

=OFFSET(INDEX(Wbs,MATCH(B1,Names,0)),0,0,COUNTIF(Names,B1),1)

It only turns out Value. Is this because I need add'ins? But thanks anyway. I don't need the list to be dynamic, but it would be cool to have it that way. I did not understand what you meant by two named ranges? Thanks a lot for helping me. I will fore sure post more on this site.