Enter number on a worksheet to copy row from another worksheet

[Guest]

I want to create data on sheet 2 of a spread sheet. Then on sheet 1 I type
in a part number (123456) in B5. The search will go to sheet 2 find that
part number then copies everything in that row on sheet 1. For example the
number 123456 is found on sheet 2 cell A957 then the contents of B957 is
placed in C5 on sheet 1 and C957 is placed in D5 and D957 is placed in E5 and
so on.

 

[Pete_UK]

Once you have your table of data on Sheet2, define a named range which
encompases it (excluding any headings) - suppose you call this range
"table". Then in Sheet1 you would enter the following formulae in the
cells specified:

C5: =VLOOKUP($B5,table,2,0)
D5: =VLOOKUP($B5,table,3,0)
E5: =VLOOKUP($B5,table,4,0)
F5: =VLOOKUP($B5,table,5,0)

Note that the only difference in the formulae is the third parameter -
this determines which column of the table you would get the data from
if a match is found. This means that you could enter the formula into
C5 and then copy it across as many columns as you need and then just
amend the third parameter. A more general formula would be:

C5: =VLOOKUP($B5,table,COLUMN()-1,0)

and then you could copy this across without having to amend the third
parameter.

Hope this helps.

Pete