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

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.

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

V!p3r,

To return the item, use

=INDEX(ItemNamesRange,MATCH(barcode,BarCodeRange,F alse)

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

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

--
HTH,
Bernie
MS Excel MVP


"V!p3r" wrote in message
...
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.

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

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" wrote:

V!p3r,

To return the item, use

=INDEX(ItemNamesRange,MATCH(barcode,BarCodeRange,F alse)

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

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

--
HTH,
Bernie
MS Excel MVP


"V!p3r" wrote in message
...
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.

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

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,F alse)) without the = sign....

--
HTH,
Bernie
MS Excel MVP


"V!p3r" wrote in message
...
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" wrote:

V!p3r,

To return the item, use

=INDEX(ItemNamesRange,MATCH(barcode,BarCodeRange,F alse)

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

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

--
HTH,
Bernie
MS Excel MVP


"V!p3r" wrote in message
...
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.

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

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.

"Bernie Deitrick" wrote:

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,F alse)) without the = sign....

--
HTH,
Bernie
MS Excel MVP


"V!p3r" wrote in message
...
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" wrote:

V!p3r,

To return the item, use

=INDEX(ItemNamesRange,MATCH(barcode,BarCodeRange,F alse)

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

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

--
HTH,
Bernie
MS Excel MVP


"V!p3r" wrote in message
...
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.