Best Of
Community Category: Best Practice
Welcome to the Best Practice Category in Community!
With so many great discussions happening we see incredible solutions being built out by our members. This category is a place to find creative solutions, tips and tricks, and best practices. The Smartsheet Community Team will collect nominated posts and put them here in Best Practice so you can easily access this content.
Nominate a Post
Have you come across a post with a helpful solution? Nominate it to be reviewed as a Best Practice!
Here’s how to do that:
- From the post, click Flag.
- In the pop-up window, choose Other.
- Leave a note for the Community Team to clarify why you think this should be a Best Practice.
Our Community Team will review the post and move it to this category, and the member who came up with the solution will receive extra points for their great contribution. Don’t forget to use the Vote Up button or mark the post as Insightful as well!
Cheers,
Smartsheet Community Team
Genevieve P.
Working with Symbol Formulas
Hello Smartsheet Community! As our Community grows, I am starting to see some trends about features and workarounds users want to learn more about. For these topics, I am starting a new series of announcements. In this series, I will write about trending topics and topics that you request! After I post about a subject, I can answer any questions you have or go into further detail on any aspect of the topic.
Welcome to the first of this series -- Working with Symbol Formulas
This post will teach you how to build formulas with the new symbols that were added a couple months ago. If you learn the basic syntax of building a few different formulas, you can take the same formula and change up the wording to make the formula work with any symbol type.
We will start with basic IF statements that work like this:
=IF(*this* is true, then do *this*, if not then do *this*)
=IF(logical_test, value_if_true, value_if_false)
Here’s a simple IF statement located in a Flag column that will enable (1 = enabled) the flag if the referenced Due Date is in the past:
=IF([Due Date]2 < TODAY(), 1, 0)
This same formula can be used with any of our new symbols. Let’s use the weather symbols and display the Stormy” symbol if the Due Date is in the past and Sunny” if it’s not:
=IF([Due Date]2 < TODAY(), “Stormy”, “Sunny”)
My next example is a little more complicated and will show you how formulas will basically stay the same across different types of symbols, like Progress Bars, Hearts, and Stop/Rewind/Play symbols.
We will be using nested IF statements, which are multiple IF statements combined in a single formula. This gives us more options for the formula results. Here’s how they work:
=IF(*this* is true, then do *this*, IF(*this* is true, do *this*, IF none are true, do *this*)))
=IF(logical_test, value_if_true, IF(second_logical_test, value_if_true, value_if_all_false))
Here’s what the formula will do in these examples:
If % Complete is 0%-24%, *first option*. If 25%-49%, *second option*. If 50%-74%, *third option*. If 75%-99%, *fourth option*. If 100%, *fifth option*
Progress Bar:
=IF([% Complete]2 < 0.25, "Empty", IF([% Complete]2 < 0.5, "Quarter", IF([% Complete]2 < 0.75, "Half", IF([% Complete]2 < 1, "Three Quarter", IF([% Complete]2 = 1, "Full")))))
Hearts:
=IF([% Complete]2 = 0, "Empty", IF([% Complete]2 < 0.25, "One", IF([% Complete]2 < 0.5, "Two", IF([% Complete]2 < 0.75, "Three", IF([% Complete]2 < 1, "Four", IF([% Complete]2 = 1, "Five"))))))
Stop/Rewind/Play:
=IF([% Complete]2 < 0.25, "Stop", IF([% Complete]2 < 0.5, "Rewind", IF([% Complete]2 < 0.75, "Play", IF([% Complete]2 < 1, "Fast Forward", IF([% Complete]2 = 1, "Pause")))))
If you can learn how to build formulas for one type of symbol column, you can build formulas for any symbol type. The trick is just learning which terms are related to what symbol. An easy way to do this is to add the symbol column to your sheet and click the dropdown arrow in one of the cells to display all the options and the wording or by checking out this Help Center article on the different symbol columns available.
Travis
List of date formula I have used repeatedly
Doing this so I have an ongoing list of functioning optimized formulas , keeping it in the community in case it helps someone else. Please feel free to comment with your own, but I'm keeping the most basic of the formulas out of this. Below are just some of the ones that I thought of now, i'll comment more as I use them.
CURRENT PERIOD
this sunday returned as date
=today()-weekday(today())+1
this quarter returned as integer 1-4 standard year
=INT((MONTH(TODAY()) + 2) / 3
NEXT PERIOD
next quarter returned as integer 1-4 standard year (also current quarter if year starts in october)
=IF(INT((MONTH(TODAY()) + 2) / 3) = 4, 1, INT((MONTH(TODAY()) + 2) / 3)+1
next month returned as integer
=if(month(today())=12,1,month(today(
LAST PERIOD
last month returned as integer
=month(date(year(today()),month(today()),1)-1
last quarter standard year returned as integer 1-4 (also current year if year starts in july)
=IF(INT((MONTH(TODAY()) + 2) / 3) = 1, 4, INT((MONTH(TODAY()) + 2) / 3)-1
L_123
Tips & Tricks for the new experience
Hi everyone,
I hope you're well and safe!
I thought it would be a good idea to start a thread with tips & tricks for the new experience.
Here's my first one, and it's a big one, IMHO.
Full-Screen View
Have you found any?
I hope that helps!
Stay safe, and have a fantastic day!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
Andrée Starå
Re: Chart Axis Customization
This is unusable because I cannot edit the axis. How do I edit the axis where the scale is correct. 0-100%
Danielle Lipson
Latest Comment in Cell
Is it possible to reference the latest comment on a row, in a cell?
And if we cannot currently do so, could this be considered an additional feature in the future? I would love to see the latest "update" on a project task as opposed to opening up the comments or manually updating the cell to reflect the latest update.
Thanks.
@daniel
Re: Free Webinars: Smartsheet Advance
@Victor Agudelo - nice meeting you earlier today on the Control Center roundtable call.
I've signed up for both Smartsheet Advance webinars - looking forward to it!
All the best,
Sandra
Sandra Guzman
Multi-Select Form Enhancements
Hi Community,
I’m pleased to tell you about two enhancements for forms that a number of you have requested.
First, you can now display your multi-select dropdown lists as checkboxes. This is a great way to help respondents fill in those fields faster (You can still display multi-select lists as dropdowns if you prefer).
Second, last month we added the option to set multiple default values for a multi-select dropdown list or contact list with multi-assign enabled. Like checkboxes, the default values can help reduce the time it takes people to fill out your form. You can also hide a multi-select field to easily add the same values to the corresponding column in your sheet every time the form is submitted.
Learn more about forms in our Learning Center.
As always, thank you to everyone who takes the time to send us feedback about forms. We read everything you send us and factor it into our roadmap decisions.
Caroline Sheldon
Re: Adding a picture to display in a form
Hello! I've had the same question for some time now and have thought of a way to address it. It's not as clean as if the image is in the form itself but does allow you to reference an image on the same page as the form.
- Create a Dashboard
- Add Widget: Web Content
- Insert FORM link
- Add Widget: Image, Title, Rich Text, etc
- Publish Dashboard
- Use the Dashboard link instead of the Form link for others to access
Now you have one page with the form AND the reference images.
Hope this works for you!
Victoria_Indimar
Re: Chart Axis Customization
I agree. We need to be ale to control our values on the bar charts. I need a 0% - 100% stacked bar chart and it automatically goes to 120%. Not acceptable.
Kirsten Merrill
