Retrieve and Check Data From Another Worksheet

[cyliyu]

I have 2 worksheets.
1) Warranty.xlsx - To track a product sent out to OEM for repair.
2) Database.xlsx - Production rejects information.

3 column, P, Q and R, were added in the database.xlsx.
These columns will check whether the s/no. and date code match in the warranty.xlsx and record the information as follows:-
1) RMA Before, P column - Yes means send out for repair before. "-" means never.
2) Warranty expired date, Q column - if P column ="Yes", stated the expired date, 3 months from the "Date of Return" in the warranty.xlsx. otherwise "-".
3) Warranty, R column - compare Q column and current date to check is the product overdue or not due.

I need help in:-
1) P column - "Yes" when both the S/No and date code matched.
2) Both workbooks kept in the same directory. how can I move the workbooks to another directory without changing the formula each time?

 

[Kenneth Hobson]

I don't see any question until the end so I will answer that. If you ask first and then explain, it is better.

I could give you 4 ways but this is the most simple.

When you create it, modify it then to use relative reference rather than absolute. e.g. In Database.xlsx P8 change:
=IF(ISNA(VLOOKUP(I8,'D:\Documents\[Warranty.xlsx]ADefect'!$G$9:$G$100,1,FALSE)),"-","Yes")
to:
=IF(ISNA(VLOOKUP(I8,[Warranty.xlsx]ADefect!$G$9:$G$100,1,FALSE)),"-","Yes")

 

[cyliyu]

change:
=IF(ISNA(VLOOKUP(I8,'D:\Documents\[Warranty.xlsx]ADefect'!$G$9:$G$100,1,FALSE)),"-","Yes")
to:
=IF(ISNA(VLOOKUP(I8,[Warranty.xlsx]ADefect!$G$9:$G$100,1,FALSE)),"-","Yes")

Thanks Kenneth for helping me on the second point.
It would be a great help especially for me just started learning Excel's formula.

 

[cyliyu]

With the helper column added, the P and Q columns works.
I am seeking help is it possible to improve by removing the helper column?