Auto-populate lookup formula based on previous cells

[Homeslice01]

Hi all,

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.

[Homeslice01]

My work computer doesn't appreciate my macro.

This is also incorporated into sheet 1(Main) just for full disclosure's sake, though hopefully won't affect the answer.

Please Login or Register  to view this content.

Apologies if the code tags don't work.

[Homeslice01]

I get the feeling that demand for expertise is far outstripping supply...

I have found another similar VBA example which is close to what I'm trying to achieve.

http://www.excelforum.com/excel-prog...o-another.html

The differences are though that the above macro requires a button to initiate. I'd prefer if this was automated. It also has set locations for the values to be both found and imported into. I'd like the macro to look for common values in both files and update a slave file accordingly. I don't know if what I want is possible, but assistance would be appreciated. At least even to explain how to think about it... Give a man a fish vs teach a man to fish and all that...