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 ...
excel
Formulas and Functions
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.

Resources