Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Summary Report of Multiple Sheets: Modified Date

Dennis Wierzbicki
Dennis Wierzbicki ✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

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

«1

Comments

  • Jill Youngberg
    Jill Youngberg ✭✭✭
    edited 03/06/17

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

  • J. Craig Williams
    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
    Dennis Wierzbicki ✭✭✭✭
    edited 03/07/17

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

  • Dennis Wierzbicki
    Dennis Wierzbicki ✭✭✭✭
    edited 03/07/17

    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
    J. Craig Williams ✭✭✭✭✭✭

    I used the entire column.

     

    Craig 

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

     

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

     

     

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

     

     

    Auto-Save.jpg

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

     

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

This discussion has been closed.