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
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
Comments
-
I would love to know how the SysAdmin can run a summary report!
-
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 -
Thanks, Craig, I'll give this a go and report back.
-
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"
-
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"
-
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.
-
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
-
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
-
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?
-
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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives