How to compare values in a table in one sheet to values in another sheet +++

Hi.

First a little background information. I'm working as a mechanical engineer in the R&D department. We're currently working on converting a lot of old files in one system to another system (Inventor to SolidWorks). To keep track of how far we've come, we use an excel tracking sheet, where all the partnumbers to be converted are listed and "stamped" as approved or in progress or just blank.

In another excel sheet, we've got the "break down" of the different assemblies/complete products.

What I would like to make Excel do for me, is to pick the first value of the table in the "break down sheet" and search the tracking sheet to see if it's there.
If it's NOT there, the cell/value should be marked red. If the value's there, I'd like excel to check the status. If it's been approved, the cell/value in the "break down sheet" should
be colored green and yellow/orange if it's in progress or just blank. Then it should continue with the next value and so on, until all the cells in the table has been compared to the
tracking sheet. Some of the cells in the table may also just be blank.

Is there an easy way to do this, either with formulas in excel, or by writing a macro or anything? I'm really not an expert on neither excel nor macros/VBA, but maybe someone
in here is up for a challenge? The formula/macro should be re-usable for different "break down sheets" and sizes of tables, so you should somehow be able to put in the "area"
where the table is.

Best regards

Stig M. Thu

Ahh, I forgot about the sample sheets. Please find the tracking sheet example and the break down sheet example here: https://www.dropbox.com/sh/x7l3t97tj...qMU8Tbwla?dl=0

I also have an update to the coloring of the cells. If the number from the break down sheet exists in the tracking sheet, the cell/value in the break down sheet should be colored green if the status is approved.
If the value exist, but has any other status than approved, it should be colored yellow/orange. If the value does not exist, the cell should be colored red.

Best regards

Stig M. Thu

Hi.
Sorry for not giving a clear description of what I meant. The tracking sheet contains a list of parts (Part numbers column) and the corresponding Assembly/assemblies in which it is located. One Assembly may consist of a number of parts and sub assemblies, which in turn consist of parts and New sub assemblies and so forth, hence the multiple Levels in the break Down sheet (it may be thought of as a file within a folder within a folder...).

So, what the formula/macro should be doing, is to look in the tracking sheet for a number that matches the values in the break Down sheet. One part or Assembly may occur multiple times. Take a screw or a nut i.e. It will typically be part of many assemblies and sub assemblies.

The job for the formula/macro will be to identify the parts and assemblies that have been approved and which of the ones that haven't yet been approved and which ones are missing. The missing ones will then have to be located and added to the tracking sheet as they're being converted.

So, I Guess there's some kind of hierarchy involved here as well. If a part/Assembly is found and is "in progress" it should be marked yellow, if it occurs another Place and has been approved, this should override the yellow marking, so that when a part has been marked green/approved, it should not get a yellow color if it's found in a "in progress"-state somewhere else in the tracking sheet.

Best regards

Stig M. Thu

WOW.
Thank you. I must say I just realized that this is propbably way out of my league when it comes to Excel, so I guess
I'll just have to sit down and study your solutions in order to MAYBE be able to learn how you did it, so that I - if I'm
lucky, will be able to reproduce something similar when it comes to the rest of the products.

Just one final question.
Let's say I have X number of assembly levels (Level 1, Level 2, Level 3 and so on).
Is it possible to have Excel do the stuff you were able to make it do for more than one
column. (Look through all "X" columns and have them marked according to the already listed rules?)

Thanks again for all the time and effort you have put into this.

Best regards

Stig M. Thu