Lookup and return specific value from list

[maddog9486]

I am having trouble comparing values and modifying the others based on the result. I have attached my sheet for your reference. Here is my scenario:

Mr. Man is allocated to several projects. If he submitted time to one it will show as "Submitted" or "Approved" depending on if his manager has approved his timesheet or not.

The system does not talk to itself to see that Mr. Man has submitted a timesheet for this week, so for the projects he did not submit time to, it shows as "Not Submitted".

I need for a macro or something to compare the Status' for this individual for this week and if one shows as "Submitted" or "Approved" the "Not Submitted" items should be changed.

The hierarchy is Submitted would be first, unless there is an Approved and then all would go to Approved (except for the Submitted items).

If anyone could assist me that would be great. I was thinking along the lines of needing to concatenate the week ending and employee id fields to get unique values and then lookup each cell in the whole column and then compare the "Approved", "Submitted" and "Not Submitted" cells.