Best Of
Re: How are users added to the User Management page of the Admin Center vs the user reports?
@Genevieve P. Thank you for your time and patience in clarifying all this for me. I have been trying to work with our account reps and Smartsheet support and have never come close to being able to explain this as you and Isaac have. I think I understand the concept now - for the most part at least.
Re: Capabilities of "Viewers" in new Model
@Trang Turtletraxx As long as the only access they have to edit data is through the dynamic view, they will not be added as provisional users.
Paul Newcome
Re: Capabilities of "Viewers" in new Model
@Trang Turtletraxx That is correct. Dynamic View interaction is currently not being monitored by the new USM.
Paul Newcome
Re: July Question of the Month - Join the conversation and receive a badge
My main goal this year is to be purposeful with my time. I have been a bit of an absent Father in my own home buried in work. While my 5yo daughter just wants to dance, or my 3 yo son wants me to wrestle. I have been achieving this so far and have seen a marked improvement in my two young children's over all attitude because of it.
Mark.poole
Re: Smartsheet recognized as a Customers' Choice in the Gartner® Peer Insights™ + Giveaway
@Monique Odom-Stearn I use it for work all day everyday but wanted to highlight something personal as well. Haha. I also have been using Smartsheet to track and predict personal and family financials with the intent of buying a house. Having it organized has been such a huge help especially for me with my wonky memory.
But the personal solution I wanted to share is a private library solution. Users are able to use a form to put in various search parameters such as title, author, media type, genre, etc.. Submitting the form re-routes them to a dashboard that allows them to refine their search, peruse the list of books meeting their requirements, and even check out / in books.
It also allows us to track which books have been borrowed by which people, how long they have been out, and a few other things to help us manage it all.
The next step is a barcode or QR code solution so that the books can just be scanned in or out, but the system is already user friendly enough that this has been bumped to a lower priority.
The [Check In/Out Link] field is a formula generating a pre-filled form link. Checking a book out only requires users to enter their name and then submit. Checking it back in is just the submit button.
Paul Newcome
Re: How are users added to the User Management page of the Admin Center vs the user reports?
@Isaac A. you are AMAZING and the first person to lay things out in a way that makes sense. THANK YOU for taking the time to explain this so clearly. I had a few clarifying questions related to your response.
I've been auditing to "clean up" the user management page, waiting a couple of days (since the reports are refreshed daily), rerun the reports and see the same thing - some users I REMOVED or Revoked Access from the User Management page still appear on the User Type and/or the Sheet Access reports while some no longer appear on any of the reports. There are also users that are NOT on the User Type or Sheet Access reports but are on the User Management page.
Is there a specific trigger or criteria that identifies when a user is added or removed from to one report, multiple reports vs the others?
- User Management Page: these are "users who are part of your plan" or "directly part of our plan" and does NOT include users from other Smartsheet plans who are shared to your sheets or assets…"
- How is a user identified as "part of our plan"?
- How do external users that do NOT have a verified domain added to the User Management page?
- User Type Report: this is "a comprehensive overview of all users associated with your plan who have signed in at least once."
- Is there a difference between a user that is "associated" with our plan vs "(directly) part" of our plan (defined for the User management page)?
- Is that an distinct different between the 2 reports? Or are those terms interchangeable?
- This report is constantly referred to as "the source of truth" to help estimate the user population pre and post USM such as you did but there are users on the User Management page that do not appear on the User Type Report.
- We created a process we plan to use under USM to run the User Type report to identify the provisional Members we will manage at True-up. An automated workflow will send a notification with a link to a form where provisional Members choose the user type they want to be after their provisional period ends (upgrading to a Member, downgrade to a Viewer or deactivate their account).
- We run the risk of some users not receiving the notification if there are discrepancies between the 2 reports (i.e. if there are users on the User Management page that do not appear on the User Type report). Any suggestion how to mitigate this risk?
- Sheet Access Report: this anyone "shared to your sheets, including external collaborators or users from other plans."
- Can you expand on what you mean by these users includes users from other plans? It was my understanding that user access and capabilities are plan specific.
Thanks in advance for any additional insight!
Re: Users Submitting From Excel to Existing Sheet
@S.Stone I believe you've got it.
Step 1: Users attach excel doc to form which submits to Sheet A.
Step 2: Data Shuttle grabs most recent attachment directly from Sheet A and adds the rows to Sheet B.
Paul Newcome
Re: Best method for linking 2 sets of data together that use forms to collate the information?
@SteCoxy – Thanks again for sharing your setup. Based on your form structure and screenshots, here’s an updated and practical approach that leverages the fact that [Scheme Name] is unique.
One-to-Many Structure Confirmed
From what I can see:
- Form 1 captures one-time scheme setup info.
- Form 2 can have multiple submissions per scheme (e.g., annually or per phase).
This makes the relationship:
One-to-Many
(One row in Form 1 → Multiple rows in Form 2)
Updated Formula-Based Solution (No Sheet 3 Needed)
If [Scheme Name] is unique in both sheets, you can pull all related values from Form 2 into Form 1 using COLLECT, JOIN, and SUMIF as shown below;
[Submission Date] =JOIN(COLLECT({Form2_Scheme_Outputs : Submission Date}, {Form2_Scheme_Outputs : Scheme Name}, [Scheme Name]@row ), CHAR(10))
[Completed apprenticeship] =JOIN(COLLECT({Form2_Scheme_Outputs : Completed apprenticeship}, {Form2_Scheme_Outputs : Scheme Name}, [Scheme Name]@row ), CHAR(10))
[Completed apprenticeship Total] =SUMIF({Form2_Scheme_Outputs : Scheme Name}, [Scheme Name]@row , {Form2_Scheme_Outputs : Completed apprenticeship})
Here, CHAR(10) is used to show multiple rows in a cell.
The far right two columns' data above is retrieved to the three columns below.
Benefits of This Setup
- Works even if multiple rows exist in Form 2 per scheme.
- Supports rolled-up metrics, such as totals or multi-line lists (e.g., all submission dates).
- Keeps your Part 1 sheet as the central view.
Optional Enhancements
- You can also use
MAX(COLLECT(...))to pull only the most recent value for a field like completion rate. - If you later need to limit or filter values (e.g., by year), consider adding a
[Year]column to Form 2 and use it as an additional condition inCOLLECT.
Re: Convert Created Date to Month/Date using Local Time
Hi @ctaft
It sounds like you are almost there.
How about using this to extract the month:
=LEFT([Audit Date]@row , 2)
It means look at Audit Date and return the first two characters.
Then for the year:
=MID([Audit Date]@row , FIND("/", [Audit Date]@row ) + 4, 2)
This means look at Audit Date, FIND the first /, count 4 characters from there (with the / being 1, mm being 2 and 3, and / being 4), return the next 2 characters.
You would then combine these to get the format you want:
="20" + MID([Audit Date]@row , FIND("/", [Audit Date]@row ) + 4, 2) + " / " + LEFT([Audit Date]@row , 2)
Hope this helps.
KPH
Re: How to generate another smartsheet asset when a specified criteria is met
This sounds like a perfect use case for Control Center. Do you have access to that? If not, you would need to leverage the API, the premium add-on Bridge, or some other similar third party app.
Paul Newcome