How to make reference to database and if true copy from database?

[Guest]

Hi,

I've got a stock list with the item, barcode and supplier numbers in, on a
new I now want to make a formula that will once I have scanned in the barcode
it will give me the item name and the supplier number in the two cells next
to the barcode that I've scanned. I want to use this to create a stock return
sheet.

 

[Bernie Deitrick]

V!p3r,

To return the item, use

=INDEX(ItemNamesRange,MATCH(barcode,BarCodeRange,False)

For the supplier number
=INDEX(SupNumRange,MATCH(barcode,BarCodeRange,False)

Biut use real ranges, like BarCodeRange might be 'DB Sheet'!$B$B etc..

 

[Guest]

Hi Bernie,

Thanks you very much, its working 110% beter than the ones i tried. There is
only one more thing. If the barcode is not in my database i would like it to
say "OLD-STOCK" instead of "#N/A". I thought changing "FALSE" to "OLD_STOCK"
wil work but it did not. is there a way that I could do this.\

Thanks again.

 

[Bernie Deitrick]

V!p3r,

You need to wrap your working formula into a longer formula:

=IF(ISERROR(working formula),"OLD-STOCK",working formula)

where working formula is

=INDEX(ItemNamesRange,MATCH(barcode,BarCodeRange,False)) without the = sign....

 

[Guest]

Bernie,

You are the "MASTER", thank you so much. I love using formula's in Excel and
everybody in the office always asks me to find an easier way to do things in
Excel. I really do appreciate your help.

Have a jolly good day.