Inventory Check In/Check Out with Forms

MMaydel

Hey Everyone,

I'm looking for how to automate a check in/check out process utilizing forms.

What I want:
Tech fills out form with the item's serial number and checks a box for if they're checking in or checking out

This creates a new row in a helper sheet (CHECK OUT)

A master inventory sheet (EQUIPMENT) update's the availability of that item based on matching the serial number

How do I get this to work based on the latest entry for that serial number on the helper sheet?

I tried =INDEX({B6NA CHECK OUT Equipment Inventory Range 1}, MATCH([Barcode Number]@row, {B6NA CHECK OUT Equipment Inventory Range 2}, 0))

{B6NA CHECK OUT Equipment Inventory Range 1} is the checkbox column indicating it's checked in

{B6NA CHECK OUT Equipment Inventory Range 2} is the column containing the serial number

I know this isn't right because if a new entry is added that matches the other criteria and the check in box isn't checked, it isn't unchecking it in the master sheet. I don't understand how to add an IF THEN statement that checks or unchecks based on if the check in box or check out box is checked in the latest entry.

Paul Newcome

If you set new form entries to go to the top of the sheet, you can use this:

=INDEX({Check In Column}, MATCH([Barcode Number]@row, {Barcode Number Column}, 0))

Paul Newcome

Are you able to provide some screenshots for context?

MMaydel

Yes, sorry.

The master sheet: The available check is what I want to check and uncheck based on entries on the sign in sign out helper form where the entries will populate via a form.

Helper form:

Paul Newcome

If you set new form entries to go to the top of the sheet, you can use this:

=INDEX({Check In Column}, MATCH([Barcode Number]@row, {Barcode Number Column}, 0))