Using MAX Formula- How to formulate it to capture infinite rows

hbookston

Hey Guys!

I have a Smartsheet that is connect to Jira. I have added two columns; Created On (with creation date column) and Last Create Date (date column).

I am trying to populate a formula that pulls one text box of when the sheet was last updated. When I attempt to enter =MAX([Created On]2:[Created On]???) it wont work. This formula only works up until the last row that has data. (Like I enter 85 where the ??? is the formula works as there is date) This may be because of the Jira automatically updating it with the connector, but is there a way I can put a "Infinite" like term at the end where I placed ??? marks to populate the date the sheet was updated if new data were to come in?

This can allow us to review if it is updating daily as it should from a generated report I plan to create using that formula box.

Any suggestions?

Im also unfamiliar with Jira (I just know that this sheet is connected to it)

 

Thanks!!!

Mike Wilday

Try using a range without specifying any row numbers, like this: 

=MAX([Created On]:[Created On]

This formula will search the whole column and find you the max date. 

hbookston

Hey Mike!

I do see that that works, but since I am entering in new data it automatically shows today's date. I am trying to start the formula on Row 2- going down to avoid capturing my edits on today's date.

 

Mike Wilday

The option I can see is manually entering the row numbers then. You'll have to update them when you add new rows. 

L_123

=IF(COUNTIF([Created On]:[Created On], @cell = [Created On]1) > 1, [Created On]1, MAX(COLLECT([Created On]:[Created On], [Created On]:[Created On], @cell <> [Created On]1)))

you can give that a try and see if it fits what you need.

L_123

I realized if the first row isn't the max then this won't necessarily be correct.

=if([created on]1 = max([[created on]:[created on]),IF(COUNTIF([Created On]:[Created On], @cell = [Created On]1) > 1, [Created On]1), MAX(COLLECT([Created On]:[Created On], [Created On]:[Created On], @cell <> [Created On]1)))

That should fix the problem. 

hbookston

Thank you for the help!

hbookston

Thank you for the help!

Mike Wilday

You're welcome. 

Paul Newcome

I am not sure exactly how your sheet is set up or how feasible this would be, but...

 

You could indent every row other than row 1 and in row 1 use a simple MAX(CHILDREN())

L_123

NP. If you have any issues implementing the formula or get an unexpected result let me know.