Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
Macros and Toolbars | Excel Discussion (Misc queries) | |||
How to copy Memo BLOB field from Interbase database | Excel Discussion (Misc queries) | |||
copy database to another sheet | Excel Worksheet Functions | |||
make existing pivot charts point at the same database | Charts and Charting in Excel |