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
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
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.
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 !
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you very much for the other options to consider. I really appreciate your efforts.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!