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

[Andy Debaser]

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

 

[mrmmickle1]

Not sure how you have your data set up but maybe something like this:

Excel 2012
	A	B	C
1	Keys	Timestamp	Status
2	Key 1	1/0/00 12:28 PM	Key Checked In
3	Key 2	1/0/00 12:28 PM	Key Checked In
4	Key 3	1/0/00 12:28 PM	Key Checked Out
5	Key 1	1/0/00 12:28 PM	Key Checked In
6	Key 2	1/0/00 12:28 PM	Key Checked In
Sheet1

Cell Formulas
Range	Formula
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...