-
Heirachy formula
I have a parent line with children underneath. I have a status column with red,yellow and green. Say I have 6 children lines under the parent line . I need for the status on the last entered children line to reflect on the parent line. If you close the Heirachy I will know what the last status was that was entered. Is…
-
Nested Sumif Formula
Hello, While we're training new employees I need to track their progress over a 2 week period using 9 different forms that are filled out at the end of each day; which used to be done on paper. There are 4 different teams that send in trainees so I thought it'd be easier to create 4 different workspaces for each team,…
-
Formulas for an automatically generated Risk Level
Hi I'm am trying to replicate the below formulas in smartsheet so it can automatically calculate the Risk Level with existing controls and with additional mitigation. The below formulas are currently being used in excel: Formula for existing controls Risk Level: =IFERROR(VLOOKUP(H7&I7,LikeliConseq, 2, FALSE),"") Formula…
-
Changing column Property Status (Red, Yellow, Green) when two columns dates are different
I am in need for the status colum 1 (red, yellow, green) to automatically change when column 2 (date) is passed column 3 (date). Can this be done with a formula? I can do "=IF([Approved Required Delivered Date]1 < [Adjusted Delivery Date]1, "Red")", however, I am sure on how to add the other two conditions in the formula.…
-
Formula help with Sum ifs
Hi, I have done a formula for Countifs =COUNTIFS({July Range unders}, <0, {July Range Manager}, [A/M]1) This works well and returns the correct data, however i now want the total sum of these entries so tried the following: =SUMIFS({July Range unders}, <0, {July Range Manager}, [A/M]1) But this is pulling back an Incorrect…
-
Trying to create an IF THEN function in smartsheet
My example is to select an material size value based on the material. Something like this: If [cell1]="4X8 Sheet" then [cell2]=32, if [cell1]="4X12 Sheet" then [cell2]=48, etc, etc. I have a dropdown list in the materials column to restrict the set of possible materials. Subsequently, I would like to take those values to…
-
Get a list of Distinct / Unique values
Hello, Gentlemen! Here is the situation: I need to get a list of distinct/unique values in a column using a formula on Smartsheet. I know how to create this formula on excel, but when i convert to Smartsheet, it simply doesn't work. The formula that I used in Excel is the following: =IFERROR(INDEX($A$2:$A$10, MATCH(0,…
-
Finding the child rows of a parent row
We keep track of team projects on 1 smart sheet. Projects are in parent rows. Task related to these projects are in children rows. We also have ongoing tasks (newsletters, etc) that have a due date of 12/31/19. Here is the formula to determine the number of ongoing projects: COUNTIFS({Project List Range 1}, "Project",…
-
Find a Match withing a Join?
How can I use MATCH to find if an instance occurs within a group of values joined with JOIN? For example, if I join the values in three columns (Red Yellow Blue), no commas just a space, and I want to MATCH the value in a cell (say, "Red"), how can I see if the joined values (Red Yellow Blue) contains Red? Right now, the…
-
Calculating Time Worked for Employees
I have a start time and end time in my smartsheet but need to calculate hours worked. Has anyone used a formula before to do this? if so can you pass along?