Tool sign in and out
I am looking to create a sign in and out process for tools. My thought is that employees will be able to fill out a form and scan a barcode on each tool to identify what tools they are signing out. Then once returned will be able to scan the barcodes again to sign the tool back in..... Hope that makes sense and looking forward to seeing your solution ideas for this.
Answers
-
Hi @Geard Ledet ,
I would recommend you do this with 2 sheets working in tandem.
The 1st sheet would list your tools and barcodes, along with columns to look at the second for whether that tool is currently in/out, who last signed it in/out and when they did this (this later one a date column). Data wise you would only need to fill in the tool and barcode columns. In the examples I have called this Tool Listing.
Columns would look something like this (the formulas will be explained later!):
The 2nd sheet would then have a columns for tool, barcode, In/Out (drop down with 2 choices), Employee Name and a system generated created date. It's up to you whether you would want to make the Employee Name column a drop down or leave it blank to have people fill out (in the example I have used this option). Column headers end up something like this:
For the next step I would put in some VLOOKUPs between the sheets.
For the 2nd sheet you only need 1:
Have the tool looked up from the 1st sheet using a cross sheet reference:
=VLOOKUP(Barcode@row, {Tool Listing lookup}, 2, false)
Your cross sheet reference being something like this:
In the 1st sheet you can then have a number of VLOOKUPS to grab the data when the 2nd is filled in:
=VLOOKUP(Tool@row, {Tool sign in/out Range 1}, 3, false)
=VLOOKUP(Tool@row, {Tool sign in/out Range 1}, 4, false)
=VLOOKUP(Tool@row, {Tool sign in/out Range 1}, 5, false)
With the cross sheet looking like this:
What these will do is tell you the data for the tool - whether it is in/out; who signed for it last, and when the last change happened.
To keep things easy for the users, then set up a form for the 2nd sheet, and change the setting on the form. As they are scanning the barcode it can be fairly straightforward (set to vertical radio buttons for ease of demonstration):
In the Form's settings, change a couple of options:
The reload will speed things up if users are taking tools in quick succession rather than needing to reload the form to do it. The New Submissions is the most important to change: your VLOOKUPs from earlier look for the first result so the newer submissions need to be higher up than the older ones, so it needs to find these first!
Your users can then be sent the link to the form (they won't need a Smartsheet sign-in to use it), which then can access via whatever method (workstation by tools?).
If you wish, you can make the Tool Listing have a status column based on if tools are in/out and if they have been out for more than a designated length of time have automation send an alert/reminder about this.
Hope this helps, and if you've any questions let me know!
-
WOW Nick, Thank you so much for the help!! I am going to put this together and see how I do with it!! This is my first time working with barcodes in smartsheets and my first time using VLOOKUPS so this is gonna be so much fun building out. Thank you again so much for the very detailed response, I think that is going to do EXACTLY what I was looking to do!!!
-
So i tried it out and my VLOOKUP formulas are not working.... I am sure its something im doing wrong....
-
Can you explain what you mean when you say it's "not working" - are you receiving an error or an incorrect result? Can you post a screen capture of what you're seeing (but block out sensitive data).
Thanks,
Genevieve
-
It was an error but after playing around with it last night I was able to figure it out and as i expected i was doing it wrong
