Thursday, August 7, 2014

yogi_Compile A List Of Available Assets (not in use -- sheet 'Current User')

                          Google Spreadsheet   Post  #1722
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Aug-07-2014
post by Jared Lemine:
Autofill available inventory
Hi everyone,

Here's what I am trying to accomplish. I want Sheets to tell me what computers are not in use. 

I have a 'MasterList' sheet within my workbook where all of the information of my inventory (computers) are listed. Information like asset tag #, serial number, RAM, computer name, and warranty expiration date are listed in this MasterList sheet. 

I have a 'Current User' sheet. I manually enter the name of the user, their department, and the asset tag #. The rest of the information like serial number, RAM, computer name, etc. is then auto-populated on 'Current User' via =VLOOKUP (which looks to the 'MasterList' to gather the information). 

I have a 'Past User' sheet. Same things applies as 'Current User'. I enter the past employees name, department, and asset tag # and =VLOOKUP populates the rest of the information based on 'MasterList'. 

On a new sheet, 'Available Assets', what formula do I use to tell me which asset tag # are free to give out to new personnel, based on what asset tag # has NOT been entered into the 'Current User' sheet? 

This 'Available Assets' sheet will essentially tell me, "Here are the computers that are available to give to new personnel. I don't need it to tell me every single piece of information about the asset tag # (computer). I would only need to know the asset tag #, serial number, and name of the computer. 

Any help would be greatly appreciated!