Use lookup to populate drop down

Hi,

this is my first post so please help me to improve my posting if I don't provide the right details first time!

I am trying to put together a stock control spreadsheet. In column A the user should enter a unique part number. I can use VLOOKUP to populate the second column which has the item description based on the part number entered in A and a table of part numbers and descriptions. What I am struggling with is to use a table of part numbers and locations to then populate a drop down in the third column - each part may appear in multiple locations (hence it populates a drop down).

I would like to ensure the sheet is easy to use so it must update quickly when the user enters data and save quickly. I also anticipate lots of entries so each row must have a drop down list generated in this way and the sheet will probably grow to thousands of lines.

Any help / advice gratefully received!

Bytsy.

Hi TMS and thanks for your suggestion.

I spent some while looking for an example to follow and found this one http://www.get-digital-help.com/2010...lues-in-excel/

I attach the sample file from that site (unique-distinct-dependent-lists1-three-columns.xls) and my own file (stock.xlsx). I have modified their concept so that Sheet1 column A is where the user selects a part # for the item they are signing out. Sheet1 column B then looks up the description form Parts sheet and location should be a drop down of possible locations for the part selected in column A.

In implementing this I have created from column A in Parts a dynamic named range of part # ('parts'). I have then followed the tutorial to produce a unique parts list in Parts column D and from this a dynamic named range of 'uniqueParts'. (My idea is that I simply update the Parts columns A and B as we add new parts). This is used to populate the drop down in Sheet1 column A. All good so far.

When I get to locations I have put the possible combinations (again to be updated periodically) into Locations column B. From this I have produced a dynamic named range of 'location' which I have tried to use to get uniqueLocations in Locations sheet column D according to the formula {=INDEX(location,MATCH(0,COUNTIF($D$1:D1,location)+(part<>Sheet1!$A$2),0))} which, according to the tutorial, should be dependent on the user selection in Sheet1 cell A2. In other words it should display the range of locations possible for the part in Sheet1 cell A2. This does not appear to work correctly as I get the single value indexed in 'location' by cell A2 not a range of possible locations. (I have highlighted Sheet1 cell A2 showing value of 8 for part number and this would index to 'location 5' in 'locations' where part 8 should have options of locations 6, 7 or 9).

Finally I can see a huge drawback (unless I am mistaken!) which is that I will need a similar lookup column for each part signed out.

I hope this is clear but please ask me if not. Any help will be very much appreciated.

Bytsy

Thanks again TMS but my understanding of the column in which this formula appears is to generate all the locations that the part in Sheet1 cell A2 can appear as a list which is then used to populate the drop down of locations in Sheet1 cell C2, which is my main need. That is why I was concerned that by working in this fashion I'm heading towards a similar column (and dynamic named range) per entry in the sign out sheet Sheet1.

I appreciate your comment about my rapid progress but I still feel I'm working in the dark - no light bulb over my head yet!