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

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!!!

Find more posts tagged with

Formulas

IT & Operations

Project Management

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

How do I get a column that shows the automated % complete for each activity?
I am struggling to get any formulas to work, I need a column that shows the percentage complete from the start date to the finish date - shouldn't be that hard but this is causing me so much grief. Every formula I find doesn't seem to work and I am not savvy enough to know how to update the formula so that it can work.…
Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?