-
Alphabetize Using Formula
Is there a way to easily alphabetize a list using a formula in smartsheet? In Excel, I would use the simple formula shown below to assign a numerical value to each unique list item. =COUNTIF([Unsorted Range],"<="&[Specific Cell Value]) This formula doesn't work in smartsheet, so I'm trying to find a solution. I am working…
-
Formula for rolling up children status to parent rows
Hello, I am trying to simply roll-up my children status' to their parent row. When using this formula: =IF(COUNTIFS(CHILDREN(), @cell = "Complete") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress")) I receive…
-
CountIF & Parent Child Relationship
Is there a way to have CountIF look at job # and return the number times a job # has been used on a sheet with respect to the Parent and ignore the children with the same job #
-
Adding to a X-Sheet INDEX(MAX(COLLECT( Formula
The original formula I had works: INDEX(MAX(COLLECT({Change Log - ADDRESS Prior DATE}, {Change Log - ADDRESS DATE CHANGE}, 1)), 1, 0) Now I am trying to add an IF statement and am getting an #InvalidOperation error. Can you recommend a change to the formula? I am trying to add an IF statement that looks at a date column…
-
Multiple if statements with And and Ors
I have multiple Ifs and I seem to have tied myself in a knot. =IF(NOT(ISDATE([Earliest Day 180]@row )), "Red"), IF(AND([Send HIE]@row = "Green", ePrescribe@row = "Green", [Receive & Reconcile]@row = "Green", [Provide Patient Access]@row = "Green"), OR(PHCDE@row <> "Green", [Query PDMP]@row <> "Green"), "Blue"),…
-
Formula to Change Symbol Color Based on % Complete
Hi all, I am trying to have the symbol color change based on the % Complete on a task. I have gotten it to work for 3 colors (Red/Yellow/Green) in another sheet, but now I am trying to do the same with 4 colors, including Blue. I am looking to have symbol turn Green when % Complete is at 100%, Yellow when % Complete is…
-
Pull Attachments from One Sheet to Another
I have sheets that pulls data from the sheets, then fills in a document and attaches it to a row. Is there a way to pull those attachments to another sheet so that all of my needed documents are in one place?
-
Formula for converting text/numbers - not working
I am trying to convert a total from "total time" column into something that shows "time" (so to speak). In this example the "total time" is 135 (minutes). So in Total Time (hh:mm) I need it to read , at the very least, 2.15 (doesn't have to be in hh:mm format). When I run my own formula or auto generate a formula it only…
-
Formula for Auto Reference Number
HI cpould it be possible to indicate time hour and minute in a formulated column i have sample formula in here but its still unparsable i able to create and test with some helper columns but still didnt work =IF([Case Type]@row = "Internal Service Issue", "ISI", IF([Case Type]@row = "External Service Issue", "ESI",…
-
How Can I Sum the Price of Multiple (join-collected) Items
I have two columns; one consisting of a list, using Join/Collect, of items, and one consisting of a formula to look up and sum the prices of the Join/Collected items. When the list (column 1) of items consists of only one item, the formula (column 2) works fine. When there are multiple items in Column 1, the formula does…