Apr 09 2019 07:26 AM
How can I reference a row on another sheet in the same document and display the most recent past date in one cell, and the next future date in that row in another cell?
On a different sheet sheet named "Recertification Dates" I need to scan cells G2 : BQ2 and display
This will be repeated on additional lines beyond line 2.
See attached file, and look at the sheet titled "Desired New Sheet" to see a visual explanation.
The Purpose:
I'm wanting to improve a spreadsheet I've made for our Hospice office by cleaning up the display of info and make it more user friendly. I'd like to only show the start and end dates of the certification period a patient is in.
Apr 09 2019 08:19 AM
@breinicke , that could be
max before
=LOOKUP(2,1/('Recertification Dates'!$G2:$BQ2<TODAY()),'Recertification Dates'!$G2:$BQ2)
min after
=AGGREGATE(15,6,1/('Recertification Dates'!$G2:$BQ2>TODAY())*'Recertification Dates'!$G2:$BQ2,1)
these are in new Sheet1 attached. Put formulas in right place and drag them down fro next rows.
If you are Office 365 subscriber or on Excel 2019 you may use MINIFS and MAXIFS
Apr 25 2019 01:20 PM
@Sergei Baklan I've downloaded your provided file, and thank you! However, the min next date does not seem to be working. I get a #VALUE! result for your formula.