Is there a way to have consecutive numbers entered into a column one by one on top of each other and if one of the numbers is not the same as the first but them in a different column? Example: Column A Column B ABC123 ABC123 ABC123 ABC123 DEF456 ABC123 Now once DEF456 is entered Excel knows since it is different to put it in column B or whatever column is specified. I was thinking using some IF functions but how do I set it where Excel knows to put the different number(s) into a seperate column?
so in column A you will have the same thing, and one cell may be different, because of this on column B you will NOT increment, or will you start renumbering again? Row A, same, one value in B Row B, same, increment A2 Row C, same, increment B2 Row D, same, increment C2 ... Row J, different, what happens to I2? Row K, same, what happens to J2?
The pull-down method will almost do it, but for the different values you may need a formula. Read: http://www.mvps.org/dmcritchie/excel/fillhand.htm
Assuming you've only got two columns, I'd do it like this: have a hidden column (we'll make that column C), and then A and B. Have all numbers entered in the hidden column C. In A1, write "=C1". In A2, write "=if(C2=C$1,C2,""). Leave B1 blank. In B2, write, "=if(C2=C$1,"",C2). Copy the formulas all the way down. Did I completely misunderstand the question?
OK, well there is a bar code scanner and it scans bar codes. The scanner automatically put the bar code into the first cell and then the one under that and so on. Every bar code is supposed to be the same because each box scanned on a pallet is the same product or is supposed to be. If one or more happen not to be the same as the first one scanned I need those bar codes placed in the second column. Example: Column A 1W567P432E 1W567P432E 1W567P432E 1W567P432E C9004Z888R I would need Excel to place that last bar code in a designated column because it is different and continue to scan the products and place any other bar codes that differ from the first in the designated column as well. Can I do this with formulas or will I have to write some VB code (isn't that what Excell is in?) to do this task? I am taking this project step by step, there are more and it gets more complicated down the way.
You're right it worked but here is a question; how do I hide the column and when the program is opened how do I make it start in the hidden column so that is where new bar codes being scanned go. Example, using that method I typed a code into the first row in Column C then it showed up in A while staying in C as well but when I tried to put code in the column A the formulas didn't work. So is there anyway to make Excel have the cursor start in the hidden column and be ready scan and not manually do it?
Not exactly the cursor, the blinking line that is in a cell when you double click it, what is that called?
Well you can make column A your master and B and C the 2 output columns, if the bar code reader is going to put everything in column A. Then you can hide column A if it will confuse people. If the barcode reader doesn't like it when you hide a column, you can reduce the width to zero, or put it on a different sheet or even in a different file (though I don't recommend that; it'd cause other headaches). What other complications have you got? I make my living bending Excel to my will.
Yeah, I probably will have several more questions about this project. Is there anyway to set a cell where if a certain value is entered into that cell the cursor (not the mouse cursor but the blinking line inside a cell that enters data) moves to a specified cell? Example: I am finished scanning the items and I have this: ABC123 in cell 1 ABC123 in cell 2 ABC123 in cell 3 ABC123 in cell 4 now in cell 5 I want to enter, let's say, the number 1, once I put a 1 in that cell the cursor goes to another cell to start another scan with manually having to click in that cell. Is that possible or will I have to code to do that?
I would say there is probably no way to do that with formulas. You may be able to write some VB code to do that, but someone else would have to advise you on that.
OK JV, I was able to figure everything else out but I have a question you may be able to help me with. The formulas that I put into columns A and B, is there any way to lock or protect those cells so somebody cannot come along and accidentally erase the formulas or put data into those cells that would erase the formulas?
Yes. If there are cells you want to be unlocked, highlight those cells, right-click and select Format Cells.... Go to the Protection tab and uncheck the "Locked" box. When you've unlocked those you don't want locked (if any), go to Tools > Protection > Protect Sheet. You can put on a password or not, as you like. Note, it is not completely idiot-proof, but should eliminate 95% of your trouble.
Hey JV, thanks for all your info. I have one more question, how do you freeze individual cells and not panes? If I freeze a cell, it stays in place but other cells under it scroll.
I don't think you can freeze a single cell without freezing the other ones in the same row and/or column. You must do an entire row, column, or both.
