search for latest date
 

Excel 2003
I have a summary sheet that has name, start date and finish date columns
which are post linked from another workbook. It can have the same name
entered up to 5 times down the summary sheet. In the same workbook as my
summary sheet, I have a receipts worksheet that uses the start and finish
dates off the summary sheet. A start date appears with every name no matter
how many times that name is repeated down through the column. However, the
finish date only appears once. My problem is that the receipts sheet gets a
#value error for the finish date. I presume this is due to the search
stopping at the first entry it finds for that name and because it has no
finish date for that row, it can't put a date in. I don't want to have to
change the lookup formula in the receipts sheet as there are so many of them.
My solution is this, but am stuck for the right formula.
I have created an extra column for the finish date on the summary sheet and
cut and pasted the post linked finish dates from the other workbook. I now
want to use my original finish date column (D), that the receipts workbook
uses to lookup the date, and get column (D) to look up the name in column B
and search for the latest date in the post linked column (E) and insert that
date into column (D). It hopefully won't matter that the 5 entries would all
show the same finish date.
see example below:
B (Name), C (Start date-used by receipts sheet), D (Finish date-used by
receipts sheet), E (Finish date-the hidden column, with data post linked from
other workbook)

Formula would be in cell D2
Have tried quite a few formula's but I am just not getting the latest date.
It still gliches on the first entry with no finish date. Can anyone help me
please? Does it also create a problem that the post linked column E finish
date would have alot of zero's in it due to no data from the other workbook?

try
=sumproduct(--(Name range=name),--(finish date range<""),finish date range)
the --( changes the logical True false to a numeric 1 0.
the arrays in each section have to be the same size and you cannot use the
shorthand column (row) designations. A1:B65536 will work A:B wont


"Sue" wrote:

Excel 2003
I have a summary sheet that has name, start date and finish date columns
which are post linked from another workbook. It can have the same name
entered up to 5 times down the summary sheet. In the same workbook as my
summary sheet, I have a receipts worksheet that uses the start and finish
dates off the summary sheet. A start date appears with every name no matter
how many times that name is repeated down through the column. However, the
finish date only appears once. My problem is that the receipts sheet gets a
#value error for the finish date. I presume this is due to the search
stopping at the first entry it finds for that name and because it has no
finish date for that row, it can't put a date in. I don't want to have to
change the lookup formula in the receipts sheet as there are so many of them.
My solution is this, but am stuck for the right formula.
I have created an extra column for the finish date on the summary sheet and
cut and pasted the post linked finish dates from the other workbook. I now
want to use my original finish date column (D), that the receipts workbook
uses to lookup the date, and get column (D) to look up the name in column B
and search for the latest date in the post linked column (E) and insert that
date into column (D). It hopefully won't matter that the 5 entries would all
show the same finish date.
see example below:
B (Name), C (Start date-used by receipts sheet), D (Finish date-used by
receipts sheet), E (Finish date-the hidden column, with data post linked from
other workbook)

Formula would be in cell D2
Have tried quite a few formula's but I am just not getting the latest date.
It still gliches on the first entry with no finish date. Can anyone help me
please? Does it also create a problem that the post linked column E finish
date would have alot of zero's in it due to no data from the other workbook?

Hi bj,
I have typed in =sumproduct(--(B2:B366=name),--(E2:E366<""),E2:E366) as is
but I am getting #NAME error. Where am I going wrong?
Sue

"bj" wrote:

try
=sumproduct(--(Name range=name),--(finish date range<""),finish date range)
the --( changes the logical True false to a numeric 1 0.
the arrays in each section have to be the same size and you cannot use the
shorthand column (row) designations. A1:B65536 will work A:B wont


"Sue" wrote:

Excel 2003
I have a summary sheet that has name, start date and finish date columns
which are post linked from another workbook. It can have the same name
entered up to 5 times down the summary sheet. In the same workbook as my
summary sheet, I have a receipts worksheet that uses the start and finish
dates off the summary sheet. A start date appears with every name no matter
how many times that name is repeated down through the column. However, the
finish date only appears once. My problem is that the receipts sheet gets a
#value error for the finish date. I presume this is due to the search
stopping at the first entry it finds for that name and because it has no
finish date for that row, it can't put a date in. I don't want to have to
change the lookup formula in the receipts sheet as there are so many of them.
My solution is this, but am stuck for the right formula.
I have created an extra column for the finish date on the summary sheet and
cut and pasted the post linked finish dates from the other workbook. I now
want to use my original finish date column (D), that the receipts workbook
uses to lookup the date, and get column (D) to look up the name in column B
and search for the latest date in the post linked column (E) and insert that
date into column (D). It hopefully won't matter that the 5 entries would all
show the same finish date.
see example below:
B (Name), C (Start date-used by receipts sheet), D (Finish date-used by
receipts sheet), E (Finish date-the hidden column, with data post linked from
other workbook)

Formula would be in cell D2
Have tried quite a few formula's but I am just not getting the latest date.
It still gliches on the first entry with no finish date. Can anyone help me
please? Does it also create a problem that the post linked column E finish
date would have alot of zero's in it due to no data from the other workbook?