-
Check a Box, Reference Another Sheet, Days from today
Hello, I am working with 2 sheets. 1 sheet is providing a summary of the other sheet. I need assistance with creating the formula. On the summary sheet, I want to check a box if there is a row listed on the other sheet with the same city and has a scheduled date of 6 months from today. Formula: =IF({Site}, =Site@row,…
-
Looking for help with "Contains"
I am looking for some help with the Contains function. I currently have the following formula which is working fine. =COUNTIFS({2021 Archive Range - RFI Reason}, "Census", {2021 Archive Range Assigned To}, "JP Pedicino") I need to update it to search for CONTAINS "Census" in the 2021 Archive Range - RFI Reason range and…
-
Setting "Date Completed" column date based on status change
Hi! I'm trying to set up a formula for my Date Completed column that pre-populates the date in that field to the date where the Status on the same line = "Complete". Right now I'm using this formula: =IF(OR(Status125 = "Complete"), TODAY(), "") One problem is that it doesn't add the formula for newly added line items. Also…
-
vlookup error
I am trying to write my first vlookup formula in Smartsheet and running into an error I can't resolve. This is the sheet that I am writing the formula in: This is the formula I've written: And this is the lookup_table am referencing: The Detail Status that is my search_value is in the first column of my lookup_table. My…
-
Formula help: Same criteria, same date, multiple rows
I feel like I'm missing something obvious here, but I'm struggling to come up with the correct formula to accomplish a task. Multiple attempts have come up empty. What I'd like to do is: When there are more than one instance of the Distribution column displaying 4 stars on the same day, I'd like the checkbox in the Status…
-
COUNTIFS Fomula
I created a COUNTIFS formula in Excel which would count the values "Expired" and "Overdue" and add the total's together. Excel Formula: =SUM((COUNTIFS(C:C, "CO-CURRIC-COI",J:J,"Expired")),(COUNTIFS(C:C, "CO-CURRIC-COI",J:J,"Overdue"))) When trying to emulate it in Smartsheet, I get a #unparseable error. Not sure what I am…
-
Duration column formula: two situations
Hello, I have three date columns involved in the duration: Date Submitted Date Completed For Future Requests My current duration formula only uses the Date Submitted and Date Completed columns: =IF(Status@row <> "Complete", NETWORKDAYS([Date Submitted]@row, TODAY()), NETWORKDAYS([Date Submitted]@row, [Date Completed]@row))…
-
Formula to show whichever is less
Hello, I'm working on a formula to show the remaining duration of a task. I'd like to calculate workdays using the planned end date minus either today or the planned start date, whichever is closer to the planned end date. For example, if the planned start date is 10/29/21 and the planned end date is 11/05/21, then the…
-
Is there a formula/function to help count number of children assigned to specific parent tasks?
Hi all, I'm trying to put together a table that lists the number of sub-tasks (children) in each respective Workstream (parent) of a specific Project Plan, and also breaks these down into their respective Health Status (Green, Yellow, Red). I am aware that I can apply a "COUNTIF" formula and set the range to encompass the…
-
Numbers separate by commas read as thousands
I have ONE cell with various numbers/text that users get to input via a form. The input varies from all numbers separated by commas or can have some text as well. If it is just numbers separated by commas and there are no spaces in between those numbers and commas then cell is read as number. I need some sort of a way /…