Formula to Return Last Date in a Single-Column Range

M McFadden

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.

Genevieve P.

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

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.

M McFadden

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.

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

M McFadden

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