Summary Report of Multiple Sheets: Modified Date

Hello,

I would like to creat a report/sheet that lists multiple other sheets, and returns information on either the "Modified By" or the "Modified Date" (or both), for each sheet.

I'm trying to monitor all activity across 20+ other sheets, to tell when each sheet has been updated. I know our SysAdmin can run a Summary Sheet, but I'm not the SysAdmin.

Alternately, is there a function I can place within each of the individual sheets that would return the last date modified? I don't mean when a row is modified, I mean when ANYTHING on the sheet has been modified.

Thanks in advance - Dennis

Find more posts tagged with

Account and User Management

Comments

Jill Youngberg

I would love to know how the SysAdmin can run a summary report!

J. Craig Williams

Dennis,

If you have the Modified (Date) column available*, then this formula, somewhere in the sheet:

=MAX(Modified:Modified) + ""

will show the value of the most recent change (both date and time, as text)

Unfortunately, there is no "comparison to old value" functionality in Smartsheet, but this will at least give an idea of which sheets are being modified.

Add this formula to your sheets of concern and then build a Report to get the data from the sheets and consolidate into one place.

The [Modified By] should be the same user that updates the row with the MAX formula. If it isn't, I have a work-around for this, but testing has not found a condition where it isn't.

Hope this helps.

Craig

Dennis Wierzbicki

Thanks, Craig, I'll give this a go and report back.

Dennis Wierzbicki

Craig, I can't get the syntax correct. Is the MAX formula applied to an entire column, or a range? I tried a range, and it's not working. I get "Invalid Column Value"

J. Craig Williams

I used the entire column.

Craig

Dennis Wierzbicki

Ok, and how do you input an entire column into the arugument =MAX() ?

From the SmartSheet help file:

Syntax

MAX(range)

The MAX function takes the following argument:

range—A collection of cells from which you want to return the highest value.

Example

Example: =MAX(Cost1:Cost5)

Result: 425.75

Unfortunately, when I do this, I get the aforementioned error, "Invalid Column Value"

J. Craig Williams

Dennis,

My formula was the complete column:

=MAX(Modified:Modified) + ""

(converted to text by the + ""

ColumnName:ColumnName

Where are you putting your formula?

I would put it in a Text/Number column because I am interested in the whole text.

The MAX() is looking for the date/time value, but it helps (me) to see the whole thing.

Craig

Dennis Wierzbicki

Craig, ok, that worked for the Modified (Date). I had to change the column name to "Modified", vs the default "Modified (Date)".

Now, if I can only get the formula to work for "Modified_By". Right now, it's returning a zero, as in "0", instead of the name of the last person modifying something.

Thanks, BTW.

J. Craig Williams

Dennis,

Put this in a Contact List type column:

=[Modified_By]23

Put it on the same row as the previous formula.

Change 23 to the correct row number.

This will extract the email address from the Modified_By column.

And you are welcome

Craig

Dennis Wierzbicki

Craig, that worked. Neat!

One thing I've noticed, though, is if I just open a sheet, and don't even make any changes or save any changes, it still lists me as being the "Modified_By" person. That's kinda annoying for those in my group who have supervisory responsiblities. We might open a sheet to see what's been done, but if we do, we're now listed as the person who updated the sheet, which more or less makes the "Modified_By" cell useless.

Thoughts?

Dennis

J. Craig Williams

Dennis,

If you are not making any other changes, then what you are likely seeing is one or more cells has a formula containing TODAY().

These update when the sheet is viewed or updated (directly, not via a Report).

Because they update, the Modified_By cell updates too.

If you are not saving the sheet when you exit, the sheet should not be saving your visit. At least I thought it used to do this.

I just verified the sheet auto-saves within a few seconds of my visit

This may be a bug:

In your Personal Settings under the Settings category, there are two Auto-Save Settings.

I had the first checked and as I said, it saved when opened within seconds.

I turned it off and then opened another sheet with TODAY() functions.

The save icon turns blue (enabled) but does not auto-save.

The work-around until this is fixed would be for EVERYONE to set that option off in their Personal Settings.

Hope this helps.

Craig

Dennis Wierzbicki

Ah, that's probably exactly what's happening. I have formulae all over the sheet to calculate where today is relative to the length of the month.

Does that mean it will only do that once, for the first person to access the sheet in a given day, then afterwards, the "Modified_By" cell will show who actually updated the sheet during the day, or does every time somebody opens the cell, their name will appear there?

I suppose what I'm asking is, does this mean the more accurate title for the cell should be "Last Accessed By", of every time somebody opens the sheet, it will be their name that appears there, even if they don't change anything?

J. Craig Williams

Dennis,

It depends on how your formulas are set up.

On several sheets that I tested, it was only the first one that was updated and saved.

Craig

Dennis Wierzbicki

It's something like =DAY(TODAY())/[ColumnA]35, where [ColumnA]35 is a cell that contains the number of days in a month. Returns the % of the month that has transpired as of today.

Oh, and I also just noticed my settings auto-save when leaving a page, and after 3 minutes. Might this also be doing it - saving a sheet even when I didn't change anything?

J. Craig Williams

Dennis,

See my previous comment on Auto-Save.

I have now turned both of the auto-save features off in my system and the sheet does not auto-save immediately after loading a sheet with a TODAY() formula.

Craig