Formula to Return Last Date in a Single-Column Range


The gray cell presently contains the following formula: =IF([Due Date]96 <> "", [Due Date]96, IF([Due Date]95 <> "", [Due Date]95, IF([Due Date]94 <> "", [Due Date]94, IF([Due Date]93 <> "", [Due Date]93, IF([Due Date]92 <> "", [Due Date]92, IF([Due Date]91 <> "", [Due Date]91, "MISSING"))))))

The 6 cells below the gray cell are rows 91-96. This formula "works" in that it does return the desired result from the range (FYI - entries are made from top to bottom) but I know I have solved this in the most ham-fisted way.

Is there someone out there that can suggest a more elegant/efficient way to accomplish this same result?

Thanks in advance for any advice/suggestions.

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @M McFadden

    What about having a column in your sheet which identifies the MAX row, then using this as a criteria to pull the date from that row.

    For example, if you set an Auto-Number column on your sheet this will automatically number your rows. Then we can add a helper column looking at that Auto column to identify where this row sits in your sheet (even if it's moved around).

    This helper column would contain this column formula:

    =MATCH(Auto@row, Auto:Auto, 0)


    Now that you have your rows associated with a numbered list, you can use this formula in your grey cell:

    =INDEX([Due Date]91:[Due Date]96, MATCH(MAX(COLLECT([Helper Column]91:[Helper Column]96, [Due Date]91:[Due Date]96, @cell <> "")), [Helper Column]91:[Helper Column]96, 0))


    An easier way to do this, without needing to note the Row References, would be if all the rows below your grey row are indented/child rows (see: Hierarchy: Indent or Outdent Rows).

    If they are, then we can adjust the formula to be as follows:

    =INDEX(CHILDREN(), MATCH(MAX(COLLECT(CHILDREN([Helper Column]@row), CHILDREN(), @cell <> "")), CHILDREN([Helper Column]@row), 0))


    Then you can add/delete child rows as needed without having to update the row references:


    Let me know if this is easier than your current process!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @M McFadden

    Hope you are fine, what you are trying to do exactly, did you have a range of dates and you are trying to pull the max date?

    Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hello! Sorry, I do not have access to this sheet at the moment but, I will attempt to answer your question. I have a 6-cell range (white cells) where dates are optional. If populated, the top white cell will represents the 1st/oldest entry with each cell below that potentially representing a more recent entry in sequence. The gray cell is intended to report the most-recent entry only.

    Examples: 1) If cell 1 (top white cell) and cell 2 are populated, then cell 2 should be reported in the gray cell as the most recent entry. 2) If cell 1, cell 2 and cell 6 (bottom white cell) are populated, cell 6 should be reported in the gray cell as the most-recent entry. 3) If cell 2, cell 4 and cell 5 are populated, cell 5 should be reported in the gray cell as the most-recent entry.

    Hope this makes sense. If not, let me know and I will try to explain more thoroughly. Again, the formula I have works, I just have a feeling it is not the most efficient way to accomplish this outcome. Thanks !

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @M McFadden

    What about having a column in your sheet which identifies the MAX row, then using this as a criteria to pull the date from that row.

    For example, if you set an Auto-Number column on your sheet this will automatically number your rows. Then we can add a helper column looking at that Auto column to identify where this row sits in your sheet (even if it's moved around).

    This helper column would contain this column formula:

    =MATCH(Auto@row, Auto:Auto, 0)


    Now that you have your rows associated with a numbered list, you can use this formula in your grey cell:

    =INDEX([Due Date]91:[Due Date]96, MATCH(MAX(COLLECT([Helper Column]91:[Helper Column]96, [Due Date]91:[Due Date]96, @cell <> "")), [Helper Column]91:[Helper Column]96, 0))


    An easier way to do this, without needing to note the Row References, would be if all the rows below your grey row are indented/child rows (see: Hierarchy: Indent or Outdent Rows).

    If they are, then we can adjust the formula to be as follows:

    =INDEX(CHILDREN(), MATCH(MAX(COLLECT(CHILDREN([Helper Column]@row), CHILDREN(), @cell <> "")), CHILDREN([Helper Column]@row), 0))


    Then you can add/delete child rows as needed without having to update the row references:


    Let me know if this is easier than your current process!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Thank you very much for the other options to consider. I really appreciate your efforts.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!