PC Asset Tracker - Get value in new sheet

Dear everyone,

I need help to get value in new sheet from different sheet. The worksheet purpose to track PC/Notebook replacement with old & new information.

Data in master list:
- Column A-L are the old asset device.
- Column M-U are the new asset information.
The keyword is asset tag number and based on it's mapped to serial number and owner details.

Data in branch sheet are values from master list (Column M-U) based asset tag number, location and owner details.

Please help for the formula. Many thanks

I want to make sure I understand. So you have old asset device info on "Master" sheet. Then you want the new asset information next to it. How are you to know what matches up? Are you also going to type the new Asset Tag # (or Serial No.) in column M, then you want the rest to appear?

Also, are you still on version 2013?

I'll start by throwing this out there (could be way off depending on your answers):
in cell N3, try this:
=IFERROR(INDEX(Berlin!B$2:B$12,MATCH(Master!$M3,Berlin!$A$2:$A$12,0)),
INDEX('New York'!B$2:B$12,MATCH(Master!$M3,Berlin!$A$2:$A$12,0)))

copy this across and down.

Originally Posted by Gregb11

I want to make sure I understand. So you have old asset device info on "Master" sheet. Then you want the new asset information next to it. How are you to know what matches up?
Are you also going to type the new Asset Tag # (or Serial No.) in column M, then you want the rest to appear?

Hello Greg,
I've changed the data and added an employee ID as keyword to make it easy.
I will filter master sheet by location, create new sheet (berlin sheet)--> copy & paste the employee ID into berlin sheet and get the rest (column B to N).
still in branch sheet there are few condition:
1. If column M = expired and column N = Yes --> Retrieve data from column O to W (yelow colour).
2. If column M = Active and column N = NO --> Retrieve data from column B to N.
3. If column M = expired and column N = NO --> Retrieve data from column B to N.

Column M and N are to explained whether the user already got replacement unit or not and also to check whether old device has been returned after replacement.


Also, are you still on version 2013?
2016.
Thanks

First off, please change your profile so it's up to date with your latest version.

I'm still confused. Where are you expecting the formula to be - on the "Master" Sheet or the "Berlin" sheet.
I'll assume for the moment that you're changing data in Cols M and N on the "Master" sheet, and when they meet your criteria above, then COLS O through W will appear. In that case, try this in cell O2:

=IF(AND($M2="Expired",$N2="Yes"),INDEX(Berlin!O$2:O$12,MATCH($A2,Berlin!$A$2:$A$12,0)),"")

Even if this isn't exactly how you want it to work, maybe you can see what it's doing and alter it to work the way you need it.
Good luck.

Originally Posted by Gregb11

First off, please change your profile so it's up to date with your latest version.

I'm still confused. Where are you expecting the formula to be - on the "Master" Sheet or the "Berlin" sheet.
I'll assume for the moment that you're changing data in Cols M and N on the "Master" sheet, and when they meet your criteria above, then COLS O through W will appear. In that case, try this in cell O2:

=IF(AND($M2="Expired",$N2="Yes"),INDEX(Berlin!O$2:O$12,MATCH($A2,Berlin!$A$2:$A$12,0)),"")

Even if this isn't exactly how you want it to work, maybe you can see what it's doing and alter it to work the way you need it.
Good luck.

Hello Greg,
I'm sorry to make you confused. Content in berlin sheet actually the result what i expected.
Below are the steps:
1. Input manual all data in Master list (column A to N).
2. If device has been replaced, admin must fill column O to W with new device informations.
3. Filtering master list by location.
4. Create a new sheet exp: berlin sheet
5. Copy & Paste employee id from master list into berlin sheet (column A).
6. Retrieve data from master list for column B to N in berlin sheet.
7. There are two conditions in column M and N
If column M= expired and column N = Yes, then retrieve data from master list to fill up column O to W in berlin sheet.
The rest if not meet both condition then, no data retrieve.
I hope this will help about what we discussed. Thanks

Then on the "Berlin" tab, enter this in cell B2:

=INDEX(Master!B$2:B$6,MATCH($A2,Master!$A$2:$A$6,0))

Copy this down and across to column N.

In cell O2, enter:
=IF(AND($M2="Expired",$N2="Yes"),INDEX(Master!O$2:O$6,MATCH($A2,Master!$A$2:$A$6,0)),"")

Copy this down and across.