-1

I have created an inventory tracking system with check in/out on Google Sheets and a companion Google Form. For the checking in/out part, I have 2 separate sheets with another for a status. The status is where I'm having an issue and could use some help. I was hoping to have a status sheet that shows a particular barcode as in or out of the inventory, a column for the who checked it out if relevant, and the location. The check in/out sheets are connected to a Google Form for ease of checking in/out out in the field, but now I'm wondering if that is even necessary. Help. Inventory is obviously not my jam.

Here's a copy of my sheet.

1

You are tracking items borrowed (OUT) and returned (IN) items. You want to identify items that are currently OUT (and date taken, by whom and location). You might also use this information to record the "Borrowing Status" on the master list.

IMO, you are correct to track borrowings (OUT) and returns (IN). This is essential to provide a record of transactions as well as for accountability.

I created a sheet "Borrowing Status" and used two formula to show the status by Barcode.

This formula goes in cell A1; it generates the data in the yellow cells in the screenhow below =query({OUT_Sample!A1:E;IN_Sample!A1:E},"select Col2, count(Col2), max(Col1) where Col1 is not null group by Col2 label Col2 'Barcode', count(Col2) 'Barcode count', max(Col1) 'Out-Timestamp'")

This formula goes in cell D2; it generates the data in the green cells in the screenshot. =arrayformula(if(isodd(B2:B),arrayformula(vlookup(C2:C,OUT_Sample!A2:E,{2,1,3,4},0)),""))

Headers in cells D1, E1, F1 and G1 are entered manually.

Logic

  • if the total number of transactions for a given barcode is an even number, then the item must currently be "IN".
  • if the total number of transactions for a given barcode is an odd number, then the item must currently be "OUT".
  • if an item is "OUT", then the maximum borrowing date must be the date for the OUT transaction.

Status Report

screenshot

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.