Lookup Function - Find date last data was inputed

M

martialtiger

Hey Everyone,

Long time no see. I recently just moved and finally got settled in an
came back online! ;) Just in time too. Here's my dilemna:

Version: Excel 2000

Issue: I have a spreadsheet with dates across the top ( range G3:CO3)
Then below the dates I have a validation rule that allows only "A"
"4+" and "T" as data in the cells. What I want to do is find the lates
data inputted and then find the corresponding date it was inputted
Finally I want this information to be displayed on a separate cell.

Hope I made sense.

Have a great day
 
D

daniels012

Gonna need more information.
Show us in an example of what you mean.

a b c d e f
1
2
3
4
5
 
F

Frank Kabel

Hi
first: link does not work
second: please post you example data as plain text!
 
S

Sukhjeet

Hi MartialTige
Assumption:
The dates are entered in the cells C2:AF2 (As per your screenshot) .
The entries have to be made in the row starting from 3

In the cell where enterthe formula
=MAX(($C$2:$AF$2)*(C3:AF3<>"")

After entering the formula, press Cntrl-Shift-Enter (I dont know what u call this operation, but it turns the formula into an array formula), then, make sure curly brackets appear around the formula

The formula shud now appear as
{=MAX(($C$2:$AF$2)*(C3:AF3<>""))

This will give you the max date where cells C3:CAF3 is not blank.

Drag this formula till E13

Cia

Sukhjeet


----- martialtiger > wrote: ----

Okay.... I uploaded a screenshot here: 'Excel Screenshot
(http://jonas.magcase.com/work/ExcelForum Image.htm)

In the Last Occurred Column (E), I want to display the date of when th
last occurrence took place for that particular agent

Thanks for the help!:cool
 
R

Ron Rosenfeld

Hey Everyone,

Long time no see. I recently just moved and finally got settled in and
came back online! ;) Just in time too. Here's my dilemna:

Version: Excel 2000

Issue: I have a spreadsheet with dates across the top ( range G3:CO3).
Then below the dates I have a validation rule that allows only "A",
"4+" and "T" as data in the cells. What I want to do is find the latest
data inputted and then find the corresponding date it was inputted.
Finally I want this information to be displayed on a separate cell.

Hope I made sense.

Have a great day!
Click to expand...


If there are no blanks, then the formula:

=OFFSET(G3,,COUNTA(G4:CO4)-1)

will return the date.

If there are blanks, then the *array-entered* formula:

=OFFSET(G3,,MAX(ISTEXT(G4:CO4)*COLUMN(G4:CO4))-7)

will return the date.

To array-enter a formula, after typing or pasting the formula, hold down
<ctrl><shift> while hitting <enter>. XL will place braces {...} around the
formula.


--ron
 
M

martialtiger

Hi Guys,

I appreciate the help but I think I may not have explained i
correctly. Colum E is where I want to create a formula that will loo
for the latest entry through columns G through CO. Row 1 G through C
contains date values, so row 2 starts with the data inputs. I don'
know how to explain it beyond that. I hope you get what I'm trying t
do. Basically I want to look across the sheet and see when I put a "A
4+ or T" into a cell and then find the corresponding date for it.

Thanks
 
S

Sukhjeet

Hi
What do yu want as the result of the formula in E?
If u want the date on which the latest entry was made, you have the solution above. All the formulae given will give u the date on which the last entry was made
If the result you are looking for is - which entry (A or 4+ etc) was made, please let us know
Sukhjee

----- martialtiger > wrote: ----

Hi Guys

I appreciate the help but I think I may not have explained i
correctly. Colum E is where I want to create a formula that will loo
for the latest entry through columns G through CO. Row 1 G through C
contains date values, so row 2 starts with the data inputs. I don'
know how to explain it beyond that. I hope you get what I'm trying t
do. Basically I want to look across the sheet and see when I put a "A
4+ or T" into a cell and then find the corresponding date for it

Thanks
 
R

Ron Rosenfeld

Hi Guys,

I appreciate the help but I think I may not have explained it
correctly. Colum E is where I want to create a formula that will look
for the latest entry through columns G through CO. Row 1 G through CO
contains date values, so row 2 starts with the data inputs. I don't
know how to explain it beyond that. I hope you get what I'm trying to
do. Basically I want to look across the sheet and see when I put a "A,
4+ or T" into a cell and then find the corresponding date for it.

Thanks,
Click to expand...

In your first message to which I responded, you indicated that the dates were
in G3:CO3 so that is what my formula assumed. Along with the data being in Row
4.

What is the problem with the formula I posted, if you change the reference to
row 3 to row 1; and the reference to row 4 to row 2?


--ron
 
M

martialtiger

Sorry about that. The formula did work, but I didn't edit it the firs
time. Anyway, thanks for all the help guys. 1 last thing, how can
have the cell show blank if there is no data inputted. Right now th
formula outputs "01/00/00" if no data is found
 
R

Ron Rosenfeld

Sorry about that. The formula did work, but I didn't edit it the first
time. Anyway, thanks for all the help guys. 1 last thing, how can I
have the cell show blank if there is no data inputted. Right now the
formula outputs "01/00/00" if no data is found.
Click to expand...


=OFFSET(G3,,COUNTA(G4:CO4)-1)

was my original formula.

I've not tested it, but you could try something like:


IF(COUNTA(G4:CO4)=0,"",OFFSET(G3,,COUNTA(G4:CO4)-1))

(edited to reflect the correct lines).

Or you could use conditional formatting and format the text to be the same
color as the background if COUNTA(G4:CO4)=0


--ron
 
M

martialtiger

Awesome! Thanks for the help again. This is what my formula ending u
looking like.
{=IF(COUNTA(I3:CT3)=0,"",MAX((I1:CT1)*(I3:CT3<>"")))}
Click to expand...

You guys are great! :cool
 
Top