I have a spreadsheet with a list of tasks and due dates in Google Sheets (Column E). I have an adjacent column which tracks the status of the task as either "Pending", "In Progress", or "Complete" (Column F).

What I'd like to do is have the next "Pending" or "In-Progress" date appear on the top corner of the spreadsheet so that I know as soon as I open the sheet which due date is upcoming.

Is there an "IF" statement I can use here? Or other statement that makes this possible?

Any input is appreciated. Thank you in advance!

1 Answer 1


Place this formula in a position where there are 4 free cells (2x2 matrix)

={"Pending","In Progress";min(filter(E2:E,F2:F="Pending")),min(filter(E2:E,F2:F="In Progress"))}

You will get something like this: pic

Isolating the date with a lower value among those with the word "Pending", you will find the most urgent work date (and so also for "in Progress"). If, on the other hand, you want to keep under control the last "Pending" (or "in Progress") change the min() function with the max() function

  • Thank you so much, this is exactly what I needed! Aug 11, 2022 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.