Anomaly in Excel Formula

Gaye Dougherty
Occasional Contributor

Hello. I have an Excel s/s (see attached) that I use to document Time and Attendance for a group of participants. Each participant has a card with a unique bar code number. The participant scans the card as they enter the building and they scan it when they leave the building, with the program entering their RecipientID number into Column B of the Excel s/s.


The worksheet is set up with a formula in Column A that will enter a Date and Time as the Bar Code/RecipientID number is entered into Column B.  As the cell is populated with a Bar Code/RecipientID number, the cursor drops to the next empty cell ready for the next scanned entry. All columns in the Worksheet are protected, except Column B, so no one can inadvertently enter somewhere else on the s/s.  It works very well...except!


At any given time, the formula suddenly offsets by X number of cells. So the the correct formula in cell A36:  =IF(B36<>"",IF(A36="",NOW(),A36),"")

suddenly becomes this formula in A37 :  =IF(B30<>"",IF(A37="",NOW(),A37),"")


I have tried repeatedly to replicate the anomaly, which I cannot, so I can fix it! No one should be touching either the scanner or the Tablet that displays the s/s.  And the Column A, which has the formula, is locked. 


Can you help me determine where this is going wrong?


Thank you!




1 Reply
My guess is that someone cut one or more cells in column B and pasted it to another row.