Can a formula refernce not a direct cell, but the most recent date/highest in a row?

[33CDonnelly]

I'm trying to create a workbook to track patients 6 month eligibility for our program and to stay current they need to resubmit there paperwork every 6 months. I'm trying to create a formula that will reference the most recently entered date in a column add 180 to it and subtract the current date to give me a checkmark or an X icon indicating if they are current or not. I can create the formula if I directly reference a cell such as C3 or I4 but I would like it to use the most recent such as if I have 1/1/2013 on B3 and 7/14/2013 in G3 is there a way for a formula to sort the row and use the highest number only for a formula? Currently I'm using the formula =(I4+180)-TODAY() Along with a conditional formatting Icon set.

Thank you ahead of time for any and all responses, this has had me and Google stumped all day :P

[Marcol]

Try in N3, drag/fill down

Please Login or Register  to view this content.

[33CDonnelly]

Thank you very much!!! It works perfectly for what I wanted :P

[33CDonnelly]

http://www.excelforum.com/excel-gene...y-formula.html