-
Join Ancestors
I am working my way through 'Writing Formulas in Smartsheet' module and I really don't understand the syntax of how the below would be written. Could someone provide an example of exactly how the formula would be written? Thank you!
-
Insert the month based on date
I'm trying to insert a formula to return the month i.e. January depending on a date. The formula I have inserted is =IF(MONTH([Start Date]@row) = 1, "January, IF(MONTH([Start Date]@row) = 2, "February", IF(MONTH([Start Date]@row) = 3, "March", IF(MONTH([Start Date]@row) = 4, "April", IF(MONTH([Start Date]@row) = 5, "May",…
-
Formula to flag a task if not completed before due date
I'm really struggling to get the syntax right on this. I have a column called "% Complete" and a due date column called "Finish." If the % Complete < 100 and the due date is today or already passed, I want to turn on a flag in the "At Risk" column. Ideally I'd actually like to flag it if the due date is tomorrow or already…
-
Array Formula #UNPARSEABLE
I am trying to utilize an array formula in order to calculate a net "score" based on a combination of selected responses in a Smartsheet form. Essentially: Based on pre-assigned values (1, 2, or 3) for each selectable response, I'd like the form to auto-calculate the cumulative value based on the combination of responses…
-
How can I edit the result of a VLookUp or Join(Collect) Function?
Hi Everyone, I'm currently trying to reference cell values from one sheet to another. The values in the so-called "Database Sheet" will always remain the same. The main sheet will need to reference the values in the Database Sheet through the use of a Join(Collect) Function or VLookUp within the main sheet. Fortunately,…
-
Both reference ranges in a column formula are not globally updating? Any advice?
Hello All, I have edited a column in my template with a new formula. This column NOW has a formula that pulls in data from a reference sheet (metadata sheet). I have tested this out through a provisioning test, and it works! I Love it! This is a screen shot from my template sheet. =INDEX({Metadata Range 1}, MATCH("Project…
-
Help with formula...
Hello everyone, I am trying to create a formula that could capture two parameters. The following grid aims to show the total approved change order amount by Vendor Name AND Original SOW#. For instance, the following example shows 2 different vendors (Apple and Watermelon). The total approved amount for Apple should be…
-
Formula or condition to eliminate the number "0"
Hi i am trying to build a formula in Smartsheet that make count from a cell and including other items from other. The question is how to eliminate the number zero if the count is zero? My formula it is working perfectly fine with the count but I cannot eliminate the zero. =COUNTIFS({Client Sector}, "Commercial",…
-
Set Start and End Date for Two Tasks
I'm trying to find a solution for populating a start and estimated completion date for two tasks simultaneously. For example, I use headings for Start Date, Est. Completion, and Completed. I have a task (Validate Scope) that should mirror the Start Date and Est. Completion of the Execution task (parent row). I tried using…
-
Quantity AND % Complete for children rows
I'm trying to make the parent row say the qty of children AND the % in the "Complete" column that will auto adjust once checkboxes are clicked. Note, each parent row will have different quantities of children. Right now I am using the below formula and I don't like that it gives so many decimals: =COUNTIF(CHILDREN(), true)…
