Function or Formula to auto complete adjacent data

  • Thread starter Thread starter Allan Barr
  • Start date Start date
A

Allan Barr

Working from home, and maintain a stock list of equipment
I need for work. I need to produce delivery notes of
equipment I install for customer evaluations. I have a
spreadsheet which details Product Codes, Product
Descriptions, Serial Numbers etc..

If I create a new spreadsheet (my delivery note), linked
to my main stocklist, is there a function or formula I can
use to auto comlpete the information in the remaing
fields/cells, if I enter the product code in one
field/cell ?

If the line below represents a line on my Stock List, how
do I set up my Delivery note to Auto Complete the 'Prod
Description' Cell & 'Serial Number' cell, if I enter
the 'Prod Code' in the corresponding cell on the deliverty
note ?

Prod Code Prod Description Serial Number
1234 The Product ABCD01234

Delivery Note Entry

Prod Code Prod Description Serial Number
1234 ? (Auto Complete) ? (Auto Complete)

Whatever function/formula is used, it will need to take
account of possibly hundreds of product lines, with serial
numbers etc..

If I can crack this, it will also be useful for the
regular stock takes etc... Also have BarCode 39 TTF and
BarCode Scanner, so better still.

Can anyone please offer a solution.
 
if i understand you correctly, I think Vlookup() should
work.

Say your spreadsheet with all the details of Prod codes
and serials etc is in a range A1:C500

type this formula under the Prod Description
=vlookup(A1,'[ProdDetails.XLS]Sheet1'!$A$1:$C$500,2,False)

Type this formula under the Serial Number
=vlookup(A1,'[ProdDetails.XLS]Sheet1'!$A$1:$C$500,3,False)

Prod Code Prod Description Serial Number
1234 ? (Auto Complete) ? (Auto Complete)

HTH,
John
 
John, Thank you. This works extremely well, with one
exception. I have multiple products in the 'Product Code'
column, with the same Prod Code No., however each one has
a different Serial Number. This function seems to look
for the 1st entry with that Product Code No. and fills in
that Serial No. Can You suggest a way in which I can
build in some way of differentiating items with the same
Product code, within my spreadsheet, but with different
Serial Numbers ?
I'm considering another spreadsheet design to account for
this, but any other thoughts would be much appreciated.
-----Original Message-----
if i understand you correctly, I think Vlookup() should
work.

Say your spreadsheet with all the details of Prod codes
and serials etc is in a range A1:C500

type this formula under the Prod Description
=vlookup(A1,'[ProdDetails.XLS]Sheet1'!$A$1:$C$500,2,False)

Type this formula under the Serial Number
=vlookup(A1,'[ProdDetails.XLS]Sheet1'!$A$1:$C$500,3,False)

Prod Code Prod Description Serial Number
1234 ? (Auto Complete) ? (Auto Complete)

HTH,
John

-----Original Message-----
Working from home, and maintain a stock list of equipment
I need for work. I need to produce delivery notes of
equipment I install for customer evaluations. I have a
spreadsheet which details Product Codes, Product
Descriptions, Serial Numbers etc..

If I create a new spreadsheet (my delivery note), linked
to my main stocklist, is there a function or formula I can
use to auto comlpete the information in the remaing
fields/cells, if I enter the product code in one
field/cell ?

If the line below represents a line on my Stock List, how
do I set up my Delivery note to Auto Complete the 'Prod
Description' Cell & 'Serial Number' cell, if I enter
the 'Prod Code' in the corresponding cell on the deliverty
note ?

Prod Code Prod Description Serial Number
1234 The Product ABCD01234

Delivery Note Entry

Prod Code Prod Description Serial Number
1234 ? (Auto Complete) ? (Auto Complete)

Whatever function/formula is used, it will need to take
account of possibly hundreds of product lines, with serial
numbers etc..

If I can crack this, it will also be useful for the
regular stock takes etc... Also have BarCode 39 TTF and
BarCode Scanner, so better still.

Can anyone please offer a solution.


.
Click to expand...
.
Click to expand...
 
Back
Top