How to update checked in/out status in inventory list based on latest form response

Hello everyone,

I am trying to build an inventory management system for the company I work for, where employees will be asked to fill in a form every time they borrow a piece of equipment as well as when they bring it back.
Now I've managed to set-up most things I need, including to calculate for how long an item has been out and to process the data to show me if something has not been checked back in etc..
What I still can't figure out is how to have an automatic update in the inventory list of the status of each item based on the latest form response, considering that an item can be multiple times in and out. Basically I just want in the inventory list, at the borrowing status to have "IN" or "OUT" next to an item so I can have a direct overview, without having to manually input this info every time someone fills in a form.

Hope that I've explained clearly enough and that someone has an idea. I would really appreciate it.


image 1 forum.png
image 2 forum.png
image 3 forum.png

I feel that the google solution might be similar to that in Excel.
Could you use the information in the "HOW TO ATTACH YOUR SAMPLE WORKBOOK" banner at the top of the page to upload as an Excel file?
This might help someone propose a solution.

As you only have two posts, I don't believe you will be able to post a link.
If I am correct that the Borrowing status column needs a solution, I feel that we could work with just having the values in the other columns of the sample workbook.
If google has the ability to paste as values only then you can transfer that into an Excel workbook.

You can put the Google Sheets link into a text file, and attach the text file to a response.

OR, you can rebuild a sample in this file:
https://docs.google.com/spreadsheets...wDHXSzJHYmglY/

Please check this solution given in cell G1 of the Inventory sheet:

Please Login or Register  to view this content.

Let us know if this gives the expected results.

NOTE: i also made a copy of the Data processed sheet/tab, and show how to hide the formulas in the headers. This technique works for most situations, but not all.

Also, i made some slight changes to the formulas so you can see different methods.

Note that for G1, i have changed the IFS to IF.

The reason being that you only need to get the next time after the item has been checked out.

Please create another tab in the file, and fill it with sample data and highlight where you have entered the expected results.

Please see the new v2 sheet.

Please Login or Register  to view this content.

Great,

now that you have solution, please click "Thread tools" at the top right of your initial post, and mark the enquiry as solved

There are no "comments" fields in any of the sheets/tabs of the sample file (which is now closed).

Please create a new sample file with example data that is representative of the new question you are now asking.

Hi, thank you for your answer. I recreated the sheet and in the Data sheet I added a column called "Comments", which should be pulled in the Inventory 2 sheet with the help of a formula. Unfortunately, I seem to have another error here in the formula, because in my original file I manage with that one to pull 1 word from the comment, but not the entire thing.

https://docs.google.com/spreadsheets...#gid=205510885

If you require further assistance, remove the SOLVED tag.

You need to make the Google Sheet public.