Best Of
Can we get a Jira connector Group
Hi Admins!
I've met some great folks who are using the jira connector in some very advanced smartsheets scenarios.
We'd LOVE to have our own subgroup for topics please!
Thanks and keep up the great work!
UPVOTE if you want one too!
Will Sargent
How to Implement Dynamic Dropdown among Two Sheets
A department wanted a way to take a unique list from a project column in one sheet and use that distinct listing as a dropdown for another sheet, which updated every hour.
I currently run the below script from Python Anywhere on an hourly basis, and I have shared this script so that anyone else can use it as a template for the same business use case.
Cody Holmes
Re: Control Center multiple projet creation
I hope you're well and safe!
You can add 10 individual Project creators or use a group or domain instead. I'd recommend creating and using a group to make it simpler to maintain.
Would that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
Andrée Starå
Re: Formula To Indicate A Task Is Ready Because Predecessors Are Complete [Solution]
Thanks to @Sterling Crawford, I realized I can update my row number formula and make it a column formula. https://community.smartsheet.com/discussion/69476/row-numbers
For my solution it does require one more column. You'll need a column where every value is unique. The easiest way to do that is to add an auto number column that just starts at 1 and goes up. I named my column "Auto Number".
With that column added, here is the updated formulas.
6 Column Solution (now 7 columns):
Column 1: Row ID
=MATCH([Auto Number]@row, [Auto Number]:[Auto Number], 0)
2 Column Solution (now 3 columns):
Column 1: One Formula Row ID
=", " + MATCH([Auto Number]@row, [Auto Number]:[Auto Number], 0) + ","
Tanner Polzin
Formula To Indicate A Task Is Ready Because Predecessors Are Complete [Solution]
I have seen many posts about people wanting the ability to indicate a particular task/row is ready to begin because all of the predecessors are complete (many of them archived). All of the proposed solutions had short comings (e.g. only works on rows with a single FS predecessor with no lag). Here is my full solution to the problem.
Note 1: I have not tested this extensively. It may not perform well on large sheets.
Note 2: In this example, I have chosen for this to only work with FS predecessors (because that’s what makes sense to me). However, I indicate below which formula to change so that this can work with any predecessor type.
This
solution can be done by adding a minimum of 2 columns to your existing sheet,
but I'm going to layout this explanation using 6 columns for ease of following.
I will provide the 2 column solution at the end.
Here
are the 6 column names I used: Row ID, Row ID Comma, Pred Replace FS, Pred
Count, Pred Done Count, and Ready Status
The green columns can be converted to column formulas. Unfortunately, I don’t know of a way to get a reliable row ID column using a column formula.
Column 1: Row ID
=COUNTIFS([Task Name]$1:[Task Name]@row, OR(@cell = "", @cell <> ""))
In this example, I have referenced the [Task Name] column, but any column can be used. I stole this formula from another post thanks to Paul Newcome. This formula works regardless of how you move, copy, paste rows. The only downside is that it can’t be a column formula. https://community.smartsheet.com/discussion/69476/row-numbers
Column 2: Row ID Comma
=", " + [Row ID]@row + ","
This column is the column that will actually be looked up by the counting formula. The commas are needed to avoid false positive matches (e.g. prevent finding “2” in “20”, looking up “, 2,” won’t be found in “, 20,”
Column 3: Pred Replace FS
=", " + SUBSTITUTE(Predecessors@row, "FS", ",") + ","
This formula adds the needed commas at the beginning and end of Predecessor column, as well as changing “FS” into a comma (needed to ensure a FS predecessor with a lag will still work). Modify this formula with more nested substitute formulas to make it work with other predecessor types (FF, SS, SF) as you desire.
Column 4: Pred Count
=COUNT(COLLECT([Row ID Comma]:[Row ID Comma], [Row ID Comma]:[Row ID Comma], AND(NOT(ISBLANK(@cell)), FIND(@cell, [Pred Replace FS]@row) > 0)))
This formula counts the number of rows where the [Row ID Comma] (looks at every row) is found inside the [Pred Replace FS] column (current row only).
Column 5: Pred Done Count
=COUNT(COLLECT([Row ID Comma]:[Row ID Comma], [Row ID Comma]:[Row ID Comma], AND(NOT(ISBLANK(@cell)), FIND(@cell, [Pred Replace FS]@row) > 0), [% Complete]:[% Complete], 1))
This is similar to the [Pred Count] column, except it adds an additional condition: the % Complete column must also equal 100%. This condition can be changed to point at a different column (e.g. check box column, Status column, etc.).
Column 6: Ready Status
=IF([Pred Done Count]@row = [Pred Count]@row, "Ready", "Waiting")
This column compares the [Pred Count] column and the [Pred Done Count] column. If the counts match, then all the predecessors are finished.
2 Column Solution:
Column 1: One Formula Row ID
=", " + COUNTIFS([Task Name]$1:[Task Name]@row, OR(@cell = "", @cell <> "")) + ","
I combined [Row ID] and [Row ID Comma] into a single formula. This cannot be a column formula.
Column 2: One Formula Ready Status
=IF(COUNT(COLLECT([Row ID Comma]:[Row ID Comma], [Row ID Comma]:[Row ID Comma], AND(NOT(ISBLANK(@cell)), FIND(@cell, ", " + SUBSTITUTE(Predecessors@row, "FS", ",") + ",") > 0), [% Complete]:[% Complete], 1)) = COUNT(COLLECT([Row ID Comma]:[Row ID Comma], [Row ID Comma]:[Row ID Comma], AND(NOT(ISBLANK(@cell)), FIND(@cell, ", " + SUBSTITUTE(Predecessors@row, "FS", ",") + ",") > 0))), "Ready", "Waiting")
I combined [Pred Replace FS], [Pred Count], [Pred Done Count], and [Ready Status] into a single formula. This can be converted to a column formula.
Tanner Polzin
Re: Filter by color
Instead of going through those extra steps, can't SmartSheet just develop a way to perform basic steps that Excel does such as sorting by color?
Christina Dombrowski
Re: Formula to create hyper link with display text defined
Sigh... Feels like every community post I stumble upon plays out just like this . . .
Michael.Baker
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
