Matching Value from one colum and presenting appropriate list

[hsshah]

Hey,
I have a list of differnet sites having same site name but differnet site code. Now what I want to do is that when in the first colum I type A then in the second colum only the site codes of A should appear as a selection or drop down list. Same goes for B, C, D and E.
A picture is attached to further facilitate you.
Thanks in advance.

 

[Andrew Poulsom]

If C1 is the cell in which you will enter the letter, define the name MyList that refers to:

=OFFSET(Sheet1!$B$1,MATCH(Sheet1!$C$1,Sheet1!$A:$A,FALSE)-1,0,COUNTIF(Sheet1!$A:$A,Sheet1!$C$1),1)

and use that name as the source in Data Validation.

 

[hsshah]

Sorry Andrew I couldn't understand could you explain it a lil further as i am not that rehearsed in excel.
Thanks

If C1 is the cell in which you will enter the letter, define the name MyList that refers to:

=OFFSET(Sheet1!$B$1,MATCH(Sheet1!$C$1,Sheet1!$A:$A,FALSE)-1,0,COUNTIF(Sheet1!$A:$A,Sheet1!$C$1),1)

and use that name as the source in Data Validation.

 

[Andrew Poulsom]

You don't know how to define a name?

Define and use names in formulas - Excel - Office.com