# Barcode scanning goods in/out and date and time stamped question

#### Andy Debaser

##### New Member
Hello, I am a very basic user of excel and I am trying something that is out of my depth. I work in a security control room and we sign bunches of keys and radios in and out using hand written logs recording the person who has had it issued, the key number the time and date out and time and date when it is returned. I am looking for a paperless solution.

I have got as far as generating a barcode which when scanned will add the information into a cell and time and date stamp it. Where I go from here I dont know. I need to learn how to book the keys back in using the barcode scanner and have it time and date stamped. I will also need to know outstanding keys at the end of the shift.

Many thanks for taking the time to read this. If you can help me with this or recommend some software that can do this, that would be great. Im using excel 2010 and Windows 7

Andy

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Not sure how you have your data set up but maybe something like this:

Excel 2012
ABC
1KeysTimestampStatus
2Key 11/0/00 12:28 PMKey Checked In
3Key 21/0/00 12:28 PMKey Checked In
4Key 31/0/00 12:28 PMKey Checked Out
5Key 11/0/00 12:28 PMKey Checked In
6Key 21/0/00 12:28 PMKey Checked In
Sheet1
Cell Formulas
RangeFormula
C2= IF(MOD(COUNTIF(A:A,A2),2)=0,"Key Checked In","Key Checked Out")
C3= IF(MOD(COUNTIF(A:A,A3),2)=0,"Key Checked In","Key Checked Out")
C4= IF(MOD(COUNTIF(A:A,A4),2)=0,"Key Checked In","Key Checked Out")
C5= IF(MOD(COUNTIF(A:A,A5),2)=0,"Key Checked In","Key Checked Out")
C6= IF(MOD(COUNTIF(A:A,A6),2)=0,"Key Checked In","Key Checked Out")

The formula evaluates whether the count of the keys is odd or even. If the count of the keys is even then the key is checked In. If the count is odd then they are checked out.

Then you could use simple red / green conditional formatting to signify that the keys are checked in or out. So at a glance if all were green then you would be good. If one was red then you know which one is checked out...

Last edited:

Replies
0
Views
227
Replies
0
Views
608
Replies
2
Views
1K
Replies
0
Views
612
Replies
0
Views
1K

1,203,317
Messages
6,054,709
Members
444,742
Latest member
jmartin9247

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back