Forum Discussion

AcctMgmtAdmin's avatar
AcctMgmtAdmin
Copper Contributor
Apr 25, 2024

Connecting Separate Excel sheets for Asset Management

Hi, am working on a simple asset management solution for my company using Excel. The spreadsheets will eventually become data sources for a power app that will make the asset tracking more user friendly. In the meantime, there is one thing I am stuck on. 

 

I have one sheet for laptops (plus data - RAM, SSD, warranty exp, domain, etc.) One of the columns in the laptop sheet, lists the assigned user ("Employee"). The user is chosen from a dropdown, and the data/choices comes from another sheet that lists employees. The employees list also has a column for laptop and it is supposed to list the laptop assigned to them ("Laptop [Device Name]").

 

I would like the laptop column in the employee list to update when the assigned user is entered into the laptop inventory list. OR vice versa, whatever is possible - if either are. 

 

Is this type of connection possible between two sheets?

 

Thank you for taking the time to read my post. :smile:

 

Sincerely,

Not an Excel Expert :cool:

 

Images:

Laptop Sheet:

Employee Sheet:

 

  • MAngosto's avatar
    MAngosto
    Iron Contributor

    AcctMgmtAdmin 

     

    My approach would be to insert the following formula in the Employee tab. The formula would look for the laptop name once there is an employee name on that tab as well as information about that new employee in the laptop sheet.

     

    =IFERROR(INDEX(Laptops!$A$2:$G$5,MATCH(A5,Laptops!$F$2:$F$5,0),2);"")

     

    Try inserting one new Employee in the laptops tab with all corresponding data and then insert that same employee name in A5 on employees tab. Then, in the column of laptop name put the formula.

Resources