Part Code + part no combination for list box

In excel file there are two sheets

1. Enty Sheet
2. Master Sheet

In entry there are following columns

Code | Sub Code | qty.

In Master Sheet there are following columns

Code | Sub Code


Master Data is as under

Code | Sub Code
101 | A01
101 | A02
102 | C022
102 | C021
103 | D02
103 | D03
103 | D04
104 | A02
104 | A01

In entry sheet when i ener Code in Column A , a list box / drop down showing all related values of CodeFrom Master Sheet to be shown in Column B of Entry Sheet.

Can you help me in this.

Thanks in advance.

Regards

Suhas

You need to use list validation and Vlookup, first highlight the entire list that you call code then under the word file at the top left of your screen you will see a white box showing the current cell address click this once and type Codes, now do the same for your sub codes but the name must be SubCodes no space. Now go to your Entry sheet, click the first cell that you want to use a drop down box in, now click Data on the toolbar at the top, choose Validation, from the dropdown where it says Allow choose List, a new box will open type =Codes and press return now click the cell one colum across from the first cell you clicked and type =Vlookup("A1",Subcodes,1) A1 would be the cell that you clicked first on the sheet............now highlight both the first and second cells that we worked on, to the bottom right of the highlighted cells you will see a small black cross put your mouse over it click and hold down the left mouse button and drag down for as far as you would like the information to be available..............now whenever you click A1 or A39 or any cell that you copied the information down for a little black dropdown arrow will appear click it choose a code and the subcode will automatically fill in.

Regards,
Simon

It works fine, but i want to have the values of subcodes as List box / drop down list and want to select one entry from the subcode drop down list.

How to go about.

Regards

Suhas

Just follow the first part of the instructions i gave you for the cells where you want the list box, except this time you do not ned to name the range SubCodes as you have already done this, just perform the list validation part.

Regards,
Simon

How do i restrict the sub Code list box / drop down to the values depening on part code. i.e. Only sub codes related to the part codes selected in first column should be populated in the list box.

Regards

Suhas