date reference

[Daniel Dridan]

I have two columns of data. Column A has dates and column B has random data which relates to the date. How do I get a seperate cell to show the date value in column A for the last date where data has been entered into column B?

 

[energman58]

Try

=MAX(NOT(ISBLANK(B:B))*(A:A))

Entered as an array formula (Control-shift-enter rather than enter)

It should look like this when you are done

{=MAX(NOT(ISBLANK(B:B))*(A:A))}

But dont enter the curly brackets yourself - excel puts them in for you

 

[Rick Rothstein]

If I understand your setup correctly, the last filled cell in Column A (your dates) is on the same row as the last filled cell in Column B, correct? If so, then your request simplifies to finding the contents of the last filled cell in Column A. Here is a formula (normally entered) to do that...

=LOOKUP(2,1/(A1:A65535<>""),A:A)

The A1:A65535 reference needs to be specified for XL2003 although if you can determine a maximum row number beyond which your data will never go, then change the 65535 to that row number (it will make the formula more efficient. If you are using XL2007 or later, then you can use this formula instead...

=LOOKUP(2,1/(A:A<>""),A:A)

but it would still be more efficient to establish a maximum row number and use the first formula instead.

 

[Daniel Dridan]

Cheers, that done the trick!! Thanks