Forum Discussion

Sondre19's avatar
Sondre19
Copper Contributor
Dec 22, 2022

Setting up a loanable system for laptops

Hi, everyone! 

 

I am trying to connect two lists and change the value of availability on both lists on the row that contains the same serial number. 

 

The small list is like a storage and will only have unique serial numbers, while on the other list it will give you the option to choose a serial number from the storage. meaning that the same serial number can be repeated in the big list. 

 

My intent is that you can change the value of availiability to either borrowed out, availiable or resetting. but have it syncronized with the other list 

 

Way too advanced for me atm.. but would help out a lot if I could make it work!

Thanks for all the help 

I appologize that it is in norwegian.. i can translate the whole thing if it is difficult to understand.

List for Borrowing bellow

List for storage bellow

excel
Formulas and Functions

3 Replies

  • Jamespg6's avatar
    Jamespg6
    Copper Contributor
    Dec 23, 2022

    Sondre19 One way you could do it would be through comparing dates. Scenario below:

     

    Serial Number 12345 is loaned out to a user. Part of that loaning process is to mark it as loaned on the “out” spreadsheet, giving details of who’s taken it, what department, etc, as well as the date it was taken.

     

    Then, when it’s returned, you can either book it back in by “closing” that loan entry (I.e. entering a finish date), or by logging it back in on your master sheet.

     

    I would personally suggest the idea of handling the in and out on your loan log sheet, and having a separate column that can be hidden with the following formula:

    =IF(DATELOGGEDBACKIN="",(IF(DATELOGGEDOUT="","","Out"),"In")

     

    This will mean that if both cells are empty (I.e. the row hasn’t been used yet) the cell will return nothing. If it has a logged out date, but no logged in date (I.e. the laptop is out on loan) it returns Out, and if both fields are populated (laptop has been loaned out and subsequently returned) it will return as in.

     

    Then, on your master sheet you can populate each master entry (your inventory list) with the following to find that information:

     

    "IF(COUNTIFS(Loanlist!SerialNumber,MasterList!ThisSerialNumber,LoanList!Status,"Out")=0,"In","Out")

    Replacing the following:

    • LoanList! with your Loan Log Spreadsheet
    • SerialNumber with the range your loan log spreadsheet keeps the serial numbers in
    • MasterList! with your central inventory list
    • ThisSerialNumber with the Cell that has the relevant serial number IN YOUR MASTER LIST
    • Status with the column you’ve created in your loan log spreadsheet to maintain the in/out status in the previous step.

    If you have any outstanding loans showing against a serial number, your master list should then return “Out” for that serial number. It will only return “In” when all rows with its serial in the loan log have both an out and in date populated.

     

    Hope this helps, or at least gives you some inspiration. Feel free to ask more questions if you need clarification.

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 22, 2022

    Hi Sondre19 

     

    40 views so far. This probably means nobody is really clear regarding the setup of your lists and what you actually expect to acheive. The only thing I would say for now: won't work with colors only

     

    Suggestion:
    - Setup a sample worbook with your 2 lists + show the expected result
    - Share that worbook with i.e. OneDrive or any other file sharing service
    - Post the shared link in your next reply, with info. (Excel version + OS) asked in Welcome to your Excel discussion space!

Resources