Best Of
Re: Dynamically fill a cell with text
The basic idea would be:
=INDEX(COLLECT([Column2]1:[Column5]1, [Column2]@row:[Column5]@row, @cell = MAX([Column2]@row:[Column5]@row)), 1, 1)
what do you want to do when there is 2 equal ones?
You could join them:
=JOIN(COLLECT([Column2]1:[Column5]1, [Column2]@row:[Column5]@row, @cell = MAX([Column2]@row:[Column5]@row)), ", ")
If you want this to be a column formula you would do as below:
=JOIN(COLLECT(INDEX([Column2]:[Column5], 1), [Column2]@row:[Column5]@row, @cell = MAX([Column2]@row:[Column5]@row)), ", ")
Re: Report that groups one criteria and counts another
A good way to do this would be using Parent and Child rows. Then it'll look clean and you can collapse the child rows you're not currently looking at like so:
Rows formula (goes in parent cell of each parent)
=COUNT(CHILDREN([email protected]))
Completed Count formula (goes in parent cell of each parent)
=COUNTIF(CHILDREN([email protected]), ="Completed")
% Complete formula (goes in parent cell of each parent, format column as a %)
=SUM([Completed Count]@row / [email protected])
Mike TV
Re: how to do a shift planning with smartsheet?
Hi @Joachim Mund ,
Although I didn't found any specific template that may meet your requirements, I was able to build an example project that takes advantage of some Smartsheet advanced functionality such as the Gantt view, project settings, hierarchy and conditional formatting.
In my example below, I've added each shift type in a dropdown single select column so that each value can be easily selected; I then created some test contacts in a Contact list type of Column (Employee 1-6 and Manager 1-6) that you may adjust to your own needs. By using hierarchy, you can easily sub-divide each day in all correspondent shifts and while activating dependencies, the parent row will show roll-up data from all children rows. I recommend playing around with these functionalities to best adjust your needs.
Sample of Project
Conditional formatting adjustments for task bars in Gantt view
Project settings to add labels to task bars with specific column information:
On a separate note, if you'd be interested in tracking time and expenses about each project or resource, Resource Management could prove to be quite helpful and you may want to discuss this with your Sales representative as this is a premium add-on.
I hope that this can offer you an overview of which functionalities might be helpful for your needs but if you are running into a particular road-block or would need further advise on your solution, please make sure to add some screenshots of your solution where any confidential information has been hidden.
Cheers!
Julio
Julio S.
Re: Checklist within a cell in sheet.
Hi,
Using a picklist with mutiple selection is somehow a sotluion, not a perfect one but a solution. The other solution i would think of is to create as many columns as needed checkboxs.
Re: Multiple If Statements
Let's look at a simple example. Such as a sheet with video types and results for favorites:
Let's take the first Video Type of Movie and write a simple IF statement for it:
=IF([Video Type]@row="Movie", "Forest Gump")
We've only given the TRUE result for IF the Video Type is a Movie we want it to return Forest Gump. We haven't specified what to do if it's not a Movie. Next let's write a simple formula for Cartoon:
=IF([Video Type]@row="Cartoon", "Spongebob")
Again, only giving the TRUE result for if it's a Cartoon. Now let's write one for TV Show:
=IF([Video Type]@row="TV Show", "Game of Thrones")
Now that we know what we want to nest together, let's do that. To nest IF statements, you only give the TRUE result and then you start over with the next IF where the FALSE should be. Such as this:
=IF([Video Type]@row="Movie", "Forest Gump", IF([Video Type]@row="Cartoon", "Spongebob", IF([Video Type]@row="TV Show", "Game of Thrones")
Some people only give Nested IF statements TRUE values and assume that they're always going to find a match and have a TRUE result. However, to be safe some people add in a double quotes so that if all TRUE results aren't met with any matches it'll just leave the cell empty. To do that you'd add those double quotes at the end like so:
=IF([Video Type]@row="Movie", "Forest Gump", IF([Video Type]@row="Cartoon", "Spongebob", IF([Video Type]@row="TV Show", "Game of Thrones", "")
Additionally, you don't have to close the loop on all your statements with ))). SmartSheet will fill those in for you. So you could just type this into the cell and you'll notice SmartSheet will end with ))) for you.
=IF([Video Type]@row="Movie", "Forest Gump", IF([Video Type]@row="Cartoon", "Spongebob", IF([Video Type]@row="TV Show", "Game of Thrones", ""
Lastly, for the formula example you provided I don't think you're after an IF formula with an OR function. You're probably after something like this:
=IF([How many xyz]@row = "0-3 xyz", "$50-100K", IF([xyz?]@row = "4-10 xyz", "100-300K"
Then just let SmartSheet put in the )) at the end for you.
Mike TV
Re: How can a user update the % complete from an automated email sent to task owners within the sheet?
Is there currently a formula in that column, or is it being used in the dependency settings? Either one of those things can make it so that users cannot edit in an Update Request.
Re: Formula Help Needed to Remove 0 From Results
Yes. You would put it in the third portion of each IF statement.
=IF(this is true, output this, "") + IF(this is true, output this, "") + IF(this is true, output this, "")
Re: Formula Help Needed to Remove 0 From Results
Two double-quotes with no space between them… ""
=IF(CONTAINS("Group 1 - Position 1", [email protected]) , "Group 1 - Position 1" + CHAR(10) , "") + IF(CONTAINS("Group 1 - Position 2", [email protected]) , "Group 1 - Position 2" + CHAR(10) , "") + IF(CONTAINS("Group 1 - Position 3", [email protected]) , "Group 1 - Position 3" + CHAR(10) , "")
Re: Formula Help Needed to Remove 0 From Results
As suggested by @Leibel Shuchat , set the value to "" when the conditions in your IF() statements evaluate to FALSE.
My guess is that, without the blank string "", your IF() statements were evaluating to NULL until the first string is encountered (thereafter the rest of the IF() statements would evaluate to "" or blank). It seems that the sum of NULLs in Smartsheet world equals 0, a.k.a. the Boolean value of FALSE.
Re: Highlight If Older Than 'X' Days
I think it's a great feature to request! In the meantime, I can think of at least two workarounds:
1) Format everything in your column or sheet to have a red background (or whatever you want the "late" color to be). Then, in your conditional formatting, have it change the cell background to the normal white color if it's "In the last 30 days." It's kind of backwards thinking, but should technically produce the desired effect.
2) Create a new column solely for calculating the age of a task. Your formula could look something like "=TODAY() - [Date Column]1". Then, apply your conditional formatting to the desired cell/row based on the value in this column if the value is bigger than 30.
