Row Number of Last Numerical Cell

RedMan7

New Member
Joined
Feb 15, 2019
Messages
20
Hello All,

I am having a bit of trouble, and am hoping someone can help me. I have a table below containing two columns: One with a planned submission, and another with an actual submission. If the submission isn't submitted, I input "Not Submitted" and if it is not required I input "Not Required"

What I need to do is get the date of submission of the last submitted. Is there a formula I can use here to get the Date of the "Last Actual Submission" that is a date? I.e. Automate this for any kind of table - In this case, it would be 03/01/2019 as the last items are not required.

Thanks!

Planned Submission Actual Submission
17/12/201811/01/2019
17/12/2018Not Required
17/12/2018Not Submitted
17/12/2018Not Submitted
17/12/201804/12/2018
17/12/201804/12/2018
17/12/201804/12/2018
17/12/201804/12/2018
17/12/201804/12/2018
17/12/201804/12/2018
17/12/201804/12/2018
17/12/201804/12/2018
17/12/201804/12/2018
17/12/201804/12/2018
17/12/201804/12/2018
17/12/201804/12/2018
17/12/201804/12/2018
17/12/201804/12/2018
17/12/201804/12/2018
17/12/201804/12/2018
17/12/201804/12/2018
17/12/201804/12/2018
17/12/201803/01/2019
17/12/2018Not Required
17/12/2018Not Required

<colgroup><col span="2"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
I think you need a helper column. in C2
=IF(LEFT($B2,1)="N","",ROW()-1) and copy down
Then your formula would be
=OFFSET($B1,MAX($B:$B),0)
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,406
Office Version
365
Platform
Windows
Hi, this will return the last numeric value (including dates) in column B.

=LOOKUP(9.99999999999999E+307,B:B)
 

RedMan7

New Member
Joined
Feb 15, 2019
Messages
20
Thank you all, I think I have figured it out for my purposes just using a Max function.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,142
Messages
5,466,929
Members
406,510
Latest member
wizekor

This Week's Hot Topics

Top