Hi,
I provide no guarantees with respect to referencing other workbooks (not terribly experienced with it and tend to avoid it when at all possible). That said, here's something you can try.
You could do this by setting up two dynamic named ranges in your Errors workbook. http://www.contextures.com/xlNames01.html#Dynamic describes how to do this. One should be called ErrorDate and refer to column A, and the other should be called ErrorLine and refer to column B. (Names are up to you really, but I'll use the above names below, so if you change them, be consistent.)
In your Production workbook, instead of merging A2:A9, have them each separate with the date in each field. (ie. they all say 12/1/2010 for the current date, 12/2/2010 for the next day, etc)
Then, in the Production workbook, add two named ranges as follows:- ErrorDate: =[Errors.xls]Sheet1!ErrorDate
- ErrorLine: =[Errors.xls]Sheet1!ErrorLine
Then the following formula should work in Column H:
No guarantees, but this should give you something to start with. Someone else may have a better suggestion.
S
Bookmarks