Drop Down lists based on formula??

[Shooter999]

Hi

I'm sure this is possible (and possibly been asked many times) but I cant find out how to do it!

In worksheet 1 I have a list of employee numbers (column A) and and names (Column B)and in worksheet 2 I have a list of employee numbers (column A) and their payroll contracts (Column B). 1 employee can have multiple contracts).

What I'm trying to achieve in workshet 1 is to have a dropdown list ( Column C) which returns just the contracts that relate to the employee number in column A.

Is this possible? All help and guidance greatly appreciated.

Thanks

[zbor]

Look here: http://www.contextures.com/xlDataVal02.html

[Shooter999]

Hi

THanks for the prompt reply, but I dont think that provides what I'm after.

[zbor]

Upload example workbook with your inputs and desired output.
make sure to remove all private informations (like names, wages etc) but keep similliar data important for example.

[Shooter999]

THanks.

I've uploaded an example of what I'm trying to achieve.

In Column C of Sheet1, I would like a dropdown list of all contracts attached to the employee number in column A (this data is held in Sheet2).

So for example the dropdown list for employee no 1 would have 3 entries based on the corresponding data held in Column B.

Test.xlsx

[AZ-XL]

Hi
Because I use Excel-2007 I used Name Manager. But in excel 2010 you do not need to do it
=OFFSET(Start,MATCH(A2,Employee,0),1,COUNTIF(Employee,A2),1)
Paste this formula to data validation after naming the relevant cells

PS. In sheet 2 numbers should be sorted from small to larger

[Shooter999]

Hi
Because I use Excel-2007 I used Name Manager. But in excel 2010 you do not need to do it
=OFFSET(Start,MATCH(A2,Employee,0),1,COUNTIF(Employee,A2),1)
Paste this formula to data validation after naming the relevant cells

PS. In sheet 2 numbers should be sorted from small to larger

Excellent, works a treat. Thanks a lot.

[AZ-XL]

You are welcome. Also you can click star * button (add reputation) below my avatar pic.