Automatic Data selection?

[kjs1977]

Hi all,

I'm new to using Excel, well using more advanced features.

I'm trying to create a new spreadsheet to keep a record of orders recieved.

Please take a look at the example attached. What I would like to do is:-

Enter data in the Order Sheet (Sheet 1) under Column C, from a drop down menu taken from Data Sheet (Sheet 2) Column B. I then want Excel to filter the items available from Sheet 2 that correspond to the colour chosen, and once that has been done, I want the part number to be automatically entered.

For example, I order a Red pen, I type/select Red in C2 in D2, I then want Excel to only show the items related to the Red pen Fine nib rd123, Medium nib rd234, Thick nib rd345.

I've been trying all different things, but cant figure out what to do..is it at all possible?

Thanks for your help.

Kian