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.
