Excel 'lookup' solution required...

[matpj]

Hi Folks,

I have several worksheets in my Excel spreadsheet.
two of them contain 'comment' information from our project logging database (using an MSQuery to return the results).

Worksheet 1 conatins 2 columns:
ProjectNo
LastCommentDate

Worksheet 2 contains 3 columns:
ProjectNo
CommentDate
Comment.



What I need to do on a THIRD worksheet is return the comment from worksheet 2 that has the same CommentDate as the LastCommentDate on worksheet 1.

does this make sense?
Is it possible to do with nested VLOOKUPs or similar?
I'm having real problems with it!

thanks in advance...

[pinmaster]

Hi
Assuming sheet3 has the same ProjecNo column.

=IF(Sheet2!B1=Sheet1!B1,VLOOKUP(A1,Sheet2!$A$1:$C$10,3,0),"")

HTH
JG

[matpj]

hi, thanks for the response.
sheet 3 doesn't have the same projectno column.

I could put the comment on sheet 1 column C, and I have tried although I get an error that Excel proposes a solution for..
but no results come through:

=IF(Comments!B1=LatestCommentDate!B1,VLOOKUP(A:A,Comments!$A$1:$C$10,3,0),"")

where Sheet1 is named LatestCOmmentDate and Sheet2 is named Comments

any ideas why this isn't working?

[pinmaster]

Hi
Try:

=IF(Comments!B1=LatestCommentDate!B1,VLOOKUP(A1,Comments!$A$1:$C$10,3,0),"")
copied down

$A$1:$C$10 is your lookup range, you will have to ajust it to suit your needs.

HTH
JG

[matpj]

hi,
thanks again for your response.
sill no joy. Its not returning an error, just no values.
If I sort the Comments worksheet by ProjectNo ascending and then commentdate descending, I get the first comment returned - but no others.
Below I have pasted extracts from each of the worksheets. If you have the chance, you will be able to paste these straight into excel and see the results if you apply the formula to column C of LastCommentDate sheet.
heres an extract from the LatestCommentDate sheet:

ProjectNo AGGFCT1
-A 01/03/2005 00:39
D00001 21/02/2005 21:15
D00001-A 28/02/2005 00:28
D00001-D 23/02/2005 01:14
D00002 31/01/2005 03:40
D00002-A 31/01/2005 03:40
D00004 31/01/2005 03:21
D00004-A 01/02/2005 07:38
D00006 31/01/2005 10:38
D00009 24/02/2005 08:14
D00009-A 24/02/2005 08:13
D00010 31/01/2005 16:39
D00010-A 09/02/2005 11:23
D00011 31/01/2005 09:54


and heres the comments sheet which should contain values that need to be looked up:

ProjectNo CreatedDate CommentAsText
D00001 08/03/2005 05:52 This is a sample comment...
D00001 31/01/2005 07:10 Another new comment...
D00001-A 09/03/2005 01:02 Here is a sample comment.
D00001-A 16/02/2005 07:12 Holder re-assigned to Petar Tasic Just moving it off my work view ...Michele
D00001-A 31/01/2005 01:23 Hi Marg & Janet, testing, testing
D00001-C 07/03/2005 20:35 jjdjddk
D00001-D 23/02/2005 01:14 sssss
D00002 31/01/2005 03:40 comments still work!
D00002 31/01/2005 03:22 a new comment for EVERYBODY!
D00002-A 31/01/2005 03:40 a new comment here as well...
D00002-A 31/01/2005 02:58 new comment
D00002-A 30/01/2005 00:21 I hope this all works out
D00004 31/01/2005 03:21 test
D00004-A 01/02/2005 07:38 test comment


looking at it, I think I can see why its not working. You are checking for the same project number on each row. This will not be true except on the first row, as the LatestCommentDate sheet is a grouping of the project numbers with an aggregate function to return the maximum date.
The 'Comment' worksheet is a straight extract from the table showing a seperate row for each project for each comment.

The formula needs to first search for the the project number in the 'Comment' worksheet, and then find the row that has a CommentDate equal to the date in columnB of the 'LastCommentDate' worksheet.

is this possible?
thanks for your time,
Matt

[pinmaster]

Hi
My formula assumes that the ProjectNo column on sheet 1 and 2 are the same and since they are not it would not work. I believe it can be done with either an array formula or a macro. But I'm not a professional so I can't help you there, I appolagize for that I was just trying to help. Maybe someone else has an idea.

Regards
JG