+ Reply to Thread
Results 1 to 21 of 21

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

  1. #1
    Registered User
    Join Date
    08-14-2023
    Location
    Germany
    MS-Off Ver
    google sheets
    Posts
    12

    Question 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
    Last edited by miskarg; 08-14-2023 at 04:42 PM. Reason: reformulating question in title to better clarify issue

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,302

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

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    08-14-2023
    Location
    Germany
    MS-Off Ver
    google sheets
    Posts
    12

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

    Hi, thank you for answering. The problem that I see is that the formulas in the google worksheet don't apply in an excel workbook, so when I try to copy the data to attach here, I would need to use other formulas in the "Data processed" sheet. I would gladly share a link of the google sheet if that is allowed here.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,302

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

    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.

  5. #5
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,052

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

    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/
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  6. #6
    Registered User
    Join Date
    08-14-2023
    Location
    Germany
    MS-Off Ver
    google sheets
    Posts
    12

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

    Thank you, janmorris for the providing a way for me to share the date. I filled the sample data in the file you shared.

  7. #7
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,052

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

    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.
    Last edited by janmorris; 08-21-2023 at 09:37 AM.

  8. #8
    Registered User
    Join Date
    08-14-2023
    Location
    Germany
    MS-Off Ver
    google sheets
    Posts
    12

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

    Thank you, so far this works beautifully. Also, many thanks for improving the formulas I had before and for the tip to hide them in the header. Much appreciated.

  9. #9
    Registered User
    Join Date
    08-14-2023
    Location
    Germany
    MS-Off Ver
    google sheets
    Posts
    12

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

    One question I still have is how I can modify the formula in the case where I would like to add a third or even forth option such as "reserved" or "in service". These options would still come through first in the data sheet and I would need it to show in the inventory. When I would add a 0 to the formula, that the "reserve" status appears, the "check IN/OUT" appear both just as "check".

  10. #10
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,052

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

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

  11. #11
    Registered User
    Join Date
    08-14-2023
    Location
    Germany
    MS-Off Ver
    google sheets
    Posts
    12

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

    I've added in the sheet "Data" the new responses with items being marked "Reserved" or "Service". And same as before I would like this information to appear in the "Inventory" sheet in the column "Borrowing status". Do you think something like that is possible by altering the formula you wrote before?

  12. #12
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,052

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

    Please see the new v2 sheet.

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    08-14-2023
    Location
    Germany
    MS-Off Ver
    google sheets
    Posts
    12

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

    This is amazing! Thank you so much.

  14. #14
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,052

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

    Great,

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

  15. #15
    Registered User
    Join Date
    08-14-2023
    Location
    Germany
    MS-Off Ver
    google sheets
    Posts
    12

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

    Done!
    Many many thanks once again.

  16. #16
    Registered User
    Join Date
    08-14-2023
    Location
    Germany
    MS-Off Ver
    google sheets
    Posts
    12

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

    Hi guys, not sure if to make a new post about this or continue writing here. So based on the questions from before, I have a comments field in column F of the "Data" table and I want these comments same as before to be shown in the Inventory list in a separate column next to the one showing the "borrowing status". I've tried adapting the formula from before, but the issues is that I am getting only one word from the comments and I am not sure how I could make it pull the entire comment.

    This is my formula at the moment:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  17. #17
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,052

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

    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.

  18. #18
    Registered User
    Join Date
    08-14-2023
    Location
    Germany
    MS-Off Ver
    google sheets
    Posts
    12

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

    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
    Attached Files Attached Files
    Last edited by AliGW; 10-25-2023 at 05:48 AM. Reason: URL fixed.

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2403 (Windows 11 22H2 64-bit)
    Posts
    78,155

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

    If you require further assistance, remove the SOLVED tag.

    You need to make the Google Sheet public.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  20. #20
    Registered User
    Join Date
    08-14-2023
    Location
    Germany
    MS-Off Ver
    google sheets
    Posts
    12

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

    I apologize. I made the suggested modifications.

  21. #21
    Registered User
    Join Date
    08-14-2023
    Location
    Germany
    MS-Off Ver
    google sheets
    Posts
    12

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

    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
    Link google sheets file.txt

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Send email based on form response, but from the excel data and not direct from form
    By mickgibbons1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-14-2023, 12:41 PM
  2. To get latest status based on status date per ID
    By martin_sushi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-19-2021, 10:24 AM
  3. Replies: 4
    Last Post: 05-23-2015, 12:08 PM
  4. Replies: 5
    Last Post: 03-03-2014, 01:53 AM
  5. [SOLVED] Value entered thr User form should be checked for existance and updated accordingly
    By vignesh805 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-22-2013, 04:23 AM
  6. Replies: 2
    Last Post: 12-19-2012, 11:23 PM
  7. Replies: 4
    Last Post: 04-08-2012, 09:43 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1