Working out the last value in a column

[cwilliams96]

Can anybody help? I have the following

Column A Name

Column B (This will hold the value I want)

Column C A date
Column D A date
Column E A date
etc

Per name along the row, there could be any variable number of dates running along the columns i.e Chris could have one date in column C and D, and thats it. John could have a date in just Column C.

I want to be able to go along the columns, find the last cell to have a date in, and put it into column B

Is this possible?

Thank You

Chris

 

[p45cal]

Macro or formula?

 

[stonysleep]

Assuming the dates were in C2, D2, E2 this should work

=IF(ISBLANK(E2),IF(ISBLANK(D2), C2, D2), E2)

 

[Peter_SSs]

Something like this?

Excel Workbook
	A	B	C	D	E	F	G
1
2	Name 1	15/05/11	15/05/11
3	Name 2	14/04/11	1/01/10	14/04/11
4	Name 3	16/03/11	8/07/09		28/02/10	16/03/11
5
Last date

 

[cwilliams96]

The Lookup one works great thank you, can you explain hot it works though?

I like to just get my head round these things than just using them

Thanks Again

Chris

 

[Peter_SSs]

9.99E+307 is a VERY large number (999 followed by 305 zeros). If the number being looked for is larger than any of the numbers in the lookup range the LOOKUP function returns the last number in the lookup range.

Since Dates in Excel are stored as numbers, the LOOKUP function will return the last number (Date in your case) in the lookup range.

 

[cwilliams96]

Very clever, thanks for all your help