Formula for lookup
Posted by D. Gendron on July 15, 2001 4:45 PM
We are scanning barcodes into a column C. What formula can be programmed so that when we scan (or type in the barcode), it automatically checks the previous entries in column C, if True, it goes to item and updates columns F by +1 or one item (quanity field). If false, the scanned or entered number remains entered into the entered cell and also updates the related Column F by 1?
One point further. Can Excel be programmed if True and updates Qty by 1, the cursor will jump back down to the blank spot to ready for a new entry?
Any help would be great, thanks.
Posted by Ian on July 16, 2001 8:00 AM
Hello
you use:
=COUNTIF($C$1:C1,C1), in cell F1 (if you have field headings start with the row number $C$#YourStartingRow the same as the row you place it in F). Make sure you use the $C$1 in the first part and not the next. You can then drag it the formula down as far as you want.
For neatness I'd use:
=IF(COUNTIF($C$1:C1,C1)=0,"",COUNTIF($C$1:C1,C1)) then you drag down further than the amount of barcode entries and you won't get 0's.
I this is usefull to you.
With regard to the RETURN question, you could alternatively Unlock column B in the Format Cells_Protection checkbox, if you then protect the sheet any time you want to got to the next cell e.g. B2 you can use the Tab key.
Any good to you. Some will know some code, but it works for me
Ian
