-
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.
-
Count matching values in date range.
Hello Gang, I've been losing my sleep over this formula so I thought of asking here. I need to determine whether a certain value is found in another sheet and if the date of that value if found within the date range of the matching value in the second sheet. Example Sheet 1 includes the following columns: DEP,ARR, DATE…
-
Is there an issue with using an IF statement across multiple sheets?
Hello, I am having trouble using an IF statement across multiple sheets. Basically, IF a row has a specific word, it should pull the column I need. Or was there never a link like this before? Thank you!
