| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bringing to a cell the last date an event occurred
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Bringing to a cell the last date an event occurred
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 "XLFanatico" wrote: 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Bringing to a cell the last date an event occurred
"Mike H" wrote: 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 "XLFanatico" wrote: 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. 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Bringing to a cell the last date an event occurred
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. "XLFanatico" wrote in message ... "Mike H" wrote: 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 "XLFanatico" wrote: 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. 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Bringing to a cell the last date an event occurred
"Fred Smith" wrote: 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. "XLFanatico" wrote in message ... "Mike H" wrote: 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 "XLFanatico" wrote: 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. 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 . 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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Bringing to a cell the last date an event occurred
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. "XLFanatico" wrote in message ... "Fred Smith" wrote: 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. "XLFanatico" wrote in message ... "Mike H" wrote: 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 "XLFanatico" wrote: 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. 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 . 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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Bringing to a cell the last date an event occurred
"Fred Smith" wrote: 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. "XLFanatico" wrote in message ... "Fred Smith" wrote: 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. "XLFanatico" wrote in message ... "Mike H" wrote: 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 "XLFanatico" wrote: 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. 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 . 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. . 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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| shade cell shortcut without bringing up palette | Excel Discussion (Misc queries) | |||
| how do i keep a time/date register of every change occurred in a c | Excel Worksheet Functions | |||
| What is the syntax to check if a date occurred before a referance. | Excel Discussion (Misc queries) | |||
| Bringing partial data from one cell into another | Excel Worksheet Functions | |||
| bringing data from one workbook to another using a date stamp func | Excel Worksheet Functions | |||
Linear Mode
