Conditional formatting based on linking two excel sheets by ID number? Save my career

Dear All,

This would really help me show my boss that I am a good worker and can find solutions to problems with some research and with your help, you have no idea how much this will help me and I really appreciate any help I could get.

Here is the situation:

I have two excel files with data. each file holds different types of data for a similar Item, which is identified by the same ID name in both sheets.

My question is whether I can make conditional formatting of the first excel sheet based on the other.


I attached two example excel sheets, however the databases I am dealing with have more column and rows. The main thing is for book 2 conditional formatting for the column called "missing" to be able to look at Book 1 at the column called "Available" and see whether the value is 1, if the value of item ID is 1 in Book 1 under "Available", and for the same ID in book 2 under "missing" it is also 1, I want the conditional formatting to highlight that 1 under "missing" in purple. and do nothing if the value is 0 in book 1 under available.

I did the highlighting manually just to show what I mean, I hope someone can shed a light and help me. Thank you

Book1.xlsxBook2.xlsx

Hi,

Please use the following formula in F3 in Book2.xlsx

=IF(VLOOKUP(B3,[Book1.xlsx]Sheet1!$A:$C,3,0)=D3,"Y","N")

Then use the conditional formatting D3=F3 to get the desired result.

Hope this helps..

Regards,
Chandraimage.jpg