0

Hello all I am trying to create an order form for approximately 50 people to use. Sheet one column one is going to be the area where the part numbers will be entered. Sheet two column one will be a database of part numbers. As part numbers are entered onto sheet one I would like it to auto populate the rest of the part ID. For example, here are the numbers from sheet two:

1111 abc
1112 def 
1113 fgh

On sheet one I would like the text to auto populate as the number is filled in as some of the part names are long. The reason for using sheet two as a database is the names and numbers change from time to time and I would like to easily be able to edit the master copy. Thanks for any replies.

Andrew
  • 7,602
  • 2
  • 34
  • 42
Patrick Farrier
  • 1

1 Answers1

0

I did something close to what you asked with Excel 2010.

Select cells where you will type the part numbers, go to the Data tab and click on Data Validation. There configure it to only allow elements from a list, and there select all the part numbers from sheet 2.

If you also have some description attached to the part number and you want it to appear automatically, you may want to also do this:

Suppose in both sheets the part numbers start at A2 and the description at B2. Use this formula in B2 in sheet 1 and then copy onto the cells below.

=VLOOKUP(A2;Sheet2!$A$2:$B$400,2)

Of course, replace $B$400 with the appropriate cell.

This only lets you type valid part numbers and even lets you choose one from a list if you click on that small arrow next to the cell (or press Alt-Down). The problem is that it doesn't autocomplete the part number. In order for this to happen, I'm afraid you will have to do this:

https://stackoverflow.com/a/22296329/2321042

Community
  • 1
  • 1
Andrew
  • 7,602
  • 2
  • 34
  • 42