Dynamic Cell reference in MAX() formula

Andrew Stills

I have a Project Management sheet in which I use the auto generated modified date for a clomun named "Last Updated On". I don't need the row specific date, but use the values in a MAX([column]) formula in a data roll-up sheet to determine when the project last saw any activity for management review.

Recently, I discovered my task list report does not update the RYGB risk bubble on a daily basis unless each underlying sheet is opened. However, in attempting a work around by adding a workflow to update a helper date each day, I discovered my MAX(column) formula will be incorrect since each sheet is being changed each day automagically. Thus, all projects will appear to have been worked on daily and the "Last Updated On" data point will be useless.

The simple fix would be to use a range and exclude the row with the daily updated date (As in, =MAX([Last Modified On]2:[Last Modified On]25). The only problem with this methodology is the number of rows may be different for each sheet, I.e. =MAX([Last Modified On]2:[Last Modified On]X). I tried adding a sufficiently large number, like =MAX([Last Modified On]2:[Last Modified On]1000), but this does not work since many of the cells do not exist. (Even including the 10 buffer cells at the bottom of each sheet produces an error.) It wouldn't be practical to continually update the 'X' for the number of rows in a separate sheet as the projects are being worked on.

My company has the Enterprise package with smartsheet. We do not have any add-ons at this time, so no control center or advanced functionality which might already have this worked out.

Any suggestions would be greatly appreciated.

Ryan Sides

@Andrew Stills Your current update to the helper column is being captured as a "change" to the row.

Try switching your "bump" automation to lock and unlock rows at a certain time each day. When I manually Lock/unlock it doesn't register as a change to the row and doesn't update the Modified date. See if the Lock/Unlock updates your RYGB indicators and doesn't register as a change.

Let me know. I'm interested to see if that "fakes" it out.

Andrew Stills

@Ryan Sides great idea... I've not used the lock/unlock rows in a workflow before and so I've never noticed how it would affect the modified date. I'll be interested to see if it works. That said, it may take a day or two to test it.

I'll be sure to let you know what comes of it.

Thanks for the suggestion!

Andrew Stills

Forgot to mention... I've tried doing a formula count on the rows in a helper cell, minus'ing the 10 extra rows at the bottom and referencing the number in the MAX() formula, I.e =MAX([Last Modified On]2:[Last Modified On]Helper4), where "Helper4" is the number of rows in the sheet... but this produces an Unparsable error.

Ryan Sides

@Andrew Stills Your current update to the helper column is being captured as a "change" to the row.

Try switching your "bump" automation to lock and unlock rows at a certain time each day. When I manually Lock/unlock it doesn't register as a change to the row and doesn't update the Modified date. See if the Lock/Unlock updates your RYGB indicators and doesn't register as a change.

Let me know. I'm interested to see if that "fakes" it out.

Andrew Stills

@Ryan Sides great idea... I've not used the lock/unlock rows in a workflow before and so I've never noticed how it would affect the modified date. I'll be interested to see if it works. That said, it may take a day or two to test it.

I'll be sure to let you know what comes of it.

Thanks for the suggestion!

Andrew Stills

@Ryan Sides Truly was a great idea and totally worked!

For anyone looking for a similar fix here is a screen grab of the Automation I entered. The "- -" mentioned is in a hidden Helper column helps to specify which row to run the automation on. I "Lock" and "Unlock" the row in the same automation leaving the sheet the same as it was prior to the automation with only updates to the status indicators which are run by formulas using the start & end date and completed columns.

Thanks @Ryan Sides for the helpful suggestion... I hope it helps others, as well.

Ryan Sides

@Andrew Stills Glad it worked out! Happy to help.