-
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)…
-
Dynamic Cell reference in MAX() formula
I have a Project Management sheet in which I use the auto generated modified date for a clomun named "Last Updated On". I don't need the row specific date, but use the values in a MAX([column]) formula in a data roll-up sheet to determine when the project last saw any activity for management review. Recently, I discovered…
-
Can you write a formula in column 5 to populate column 4?
Is it possible? Can you write a formula in column 5 to populate column 4? I need Column 4 to just rewrite what is in Column 3, BUT the formula will need to be in column 5. And it only needs to it to be written if column 6 has text.