I'll try to explain what I'm trying to do. (see attached)
I have an associate department (BLUE) that often, but not exclusively, sends work on to our department. They have their own excel spreadsheet, but for this example it's represented by the "BLUE_department" sheet. I have access to this data via a network.
What I want is a lookup the references in Main!Column B and match them with references in Blue_Department!Column B, then populate the value for Main! Column D when the BLUE department has completed their work. In the attached example, Main!E3 should return the value 2/09/2012. The macro will also need to be clever enough to not have a problem if the BLUE department hasn't yet finished their work and no completion date is available in their spreadsheet. Needless to say, if the work hasn't been completed by BLUE, the cells in column D will need to be empty.
I've added in the existing macro and formulas to try and replicate the real life example.
I have seen other examples with values (http://www.excelforum.com/excel-gene...n-the-cel.html) rather than formulas. Not perfect but it does kind of spell out what I'm looking for - but not clever enough to work out.
Any help greatly appreciated.