Bringing to a cell the last date an event occurred


X

XLFanatico

This is a long worksheet and need a formula, but don't know if possible.
A COLUMN B COLUMN C COLUMN
Doe, John Jan 05 a
Doe, Jane Jan 16 a
Presley, Elvis Jan 22
Doe, Jane Jan 30 a

All the previous data will be in SHEET 1
Now, in SHEET 2 :
COLUMN A = all possible names in COLUMN A SHEET 1,
written by me.
The formula will be used in COLUMN B, SHEET 2 and will tell me
which was the last date for example DOE,JANE had a letter "a"
(Jan 30 should be the result of the formula)
I'm thinking in VLOOKUP formula but don't know how to
organize it.
Thanks.
 
Ad

Advertisements

M

Mike H

Hi,

This is the 'ARRAY' formula if on the same sheet

=MAX(IF($A$1:$A$8="John Doe",IF($C$1:$C$8="a",$B$1:$B$8)))

and on another sheet with your list of names in Col A the 'ARRAY' formula

=MAX(IF(Sheet1!$A$1:$A$8=A1,IF(Sheet1!$C$1:$C$8="a",Sheet1!$B$1:$B$8)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike
 
X

XLFanatico

Mike H said:
Hi,

This is the 'ARRAY' formula if on the same sheet

=MAX(IF($A$1:$A$8="John Doe",IF($C$1:$C$8="a",$B$1:$B$8)))

and on another sheet with your list of names in Col A the 'ARRAY' formula

=MAX(IF(Sheet1!$A$1:$A$8=A1,IF(Sheet1!$C$1:$C$8="a",Sheet1!$B$1:$B$8)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike
Thanks for your interest and response MIKE.
The provided formula gives me a number "0".
And I really need as an answer is a DATE.
A DATE where the last "a" was found on DOE, JANE.
Thanks
 
F

Fred Smith

Did you enter it as an array formula? You can verify this when the formula
has braces {} around it in the formula bar.

Regards,
Fred.
 
X

XLFanatico

Fred Smith said:
Did you enter it as an array formula? You can verify this when the formula
has braces {} around it in the formula bar.

Regards,
Fred.



.
Yes, I did enter as array.
The result still a number instead of a date.
Does it make a difference if I'm using XL 2007 ?.
Sorry for the lack of information in my part.
Thanks.
 
F

Fred Smith

First, you said the result was zero. Now you say it's "a number instead of a
date". Presumably this "number" is non-zero?

If so, and you prefer a date rather than the number, then format the cell as
a date, with your preference of format.

It doesn't make any difference if you're using Excel 2007. For future posts,
however, specify that up front, because it can make a difference to the
appropriate recommendations.

Regards,
Fred.
 
Ad

Advertisements

X

XLFanatico

Fred Smith said:
First, you said the result was zero. Now you say it's "a number instead of a
date". Presumably this "number" is non-zero?

If so, and you prefer a date rather than the number, then format the cell as
a date, with your preference of format.

It doesn't make any difference if you're using Excel 2007. For future posts,
however, specify that up front, because it can make a difference to the
appropriate recommendations.

Regards,
Fred.



.
DO NOT PRESUME about zero, I said a number and the last time I
check zero was a number.
I however need a date not a number or zero.
(cells are format as date already)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top