Check and Retrieve 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. Only manage to find if S/No. Is match, but not both S/No. and date code.
2) Both workbooks kept in the same directory. how can I move the workbooks to another directory without changing the formula each time?

Capture.JPG


Thanks for the help.

[cyliyu]

It would be great if someone can help in my first point - How to search and match only when both serial number and date code are match.

As for second point, I replaced by:-
=IF(ISNA(VLOOKUP(I8,[Warranty.xlsx]ADefect!$G$9:$G$100,1,FALSE)),"-","Yes")

[vichopalacios]

Hi @cyliyu

perhaps the simplest way to solve your problem is to use an auxiliary column in each book.
let's say you use column "AA" as auxiliary

.- in Warranty.xlsx!AA9 you put this formula: = CONCATENATE(G9,H9) and copy down
.- in Database.xlsx!AA6 you put this formula: = CONCATENATE(I6,J6) and copy down

then
.- in Database.xlsx!P6 you put this formula: =IF(ISNA(VLOOKUP(AA6,[Warranty.xlsx]ADefect!$AA$9:$AA$100,1,FALSE)),"-","Yes") and copy down

[cyliyu]

Hi Vichopalacios,

Thanks for your advise.
Your formula do help to filter both S/No and Date code.
The only issues is when the same S/No and Date code happen twice or more in the Warranty.xlsx, ignore the previous warranty data and only reflect the last warranty date in the Database.xlsx P column.

I believed the formula may be complicated. Thanks.

[vichopalacios]

You can go to VBA and try to elaborate a macro.
You'd need to explain a little more the conditions and the actions you need from your worksheet.

The only issues is when the same S/No ....

for instance, using your "complain"...:
.- what to do when you find repeated rows !...

... etc.
If you have a clear idea of what you need, and you find the way to explain it to us, perhaps someone in this forum could help you.

[cyliyu]

Ok, Let me try my best to explain.

I combined the 2 workbooks into one (Sample1.xlsx) to ease my explanation.

In the warranty spreadsheet, it may happen that a product sent out for repair more than one time. e.g. January 17 & October 17. or even more.

If an S/No and date code in the database spreadsheet was used to compare with the warranty spreadsheet, it should only record the last date which is October 17 and ignore January 17 in column "Q".

I need help in how to compare both the S/No and Date Code between this 2 spreadsheet (P Column) and stated the last warranty date (Q Column).

A product S/No with different Date Code are 2 different condition and should track them separately.

[vichopalacios]

I think you can use this little code in a standard module, in Databse book.
Try it, and tell how it goes
This works using both books from the original post; not the last Sample1

Please Login or Register  to view this content.

[cyliyu]

an error pop up when executed the code.
Error lines:-

Please Login or Register  to view this content.

[vichopalacios]

It looks like there is a name mistake. probably the book's name
Make sure the names are exactly as it is written, and that both books are opened, before you run the code.
book: Warranty.xlsx
sheet: aDefect
If you find a difference, correct de code accordingly.
I tested and it runs.

[cyliyu]

Thanks, Vichopalacios.
It works.

[vichopalacios]

Glad to know it works for you.

Please mark the thread as SOLVED
If you think you've received help from someone in the forum, a ggod way to say thanks is to click on the little star icon (Add Reputation) in the lower left corner.