-
Inspect Cells Until Contents Found, Then Copy to Another Cell
I'm trying to create a column ("Latest Site Visit Notes") on my sheet that fills in with the latest site visit notes. We have existing columns for "Construction Supervisor Visit 1 Notes" thru "Construction Supervisor Visit 20 Notes". Our hundreds of projects all have various quantities of site visits performed so far. I'd…
-
Counting instances in each of the last 6 months on a rolling basis
Hello, I'm trying to count the number of times a type of dispute occurs within a month in the last 6 months in order to monitor trends. I want the months to roll so I don't need to adjust the formulas ever. My formula doesn't take the previous year into account and I'm not sure how to introduce it. This is what my…
-
How to write below formula in smartsheet
Hi . Can anyone help how to write below formula in Smartsheet
-
I need help on a multiple IF statement formula
I'm struggling to think through the logic to allow for the required scenarios. If the row is a master row - leave no flag as not at risk If the row is a 1st level sub heading - leave no flag as not at risk If the task isn't started by the start date = at risk! If the project has started but the review date is in the past =…
-
How to automate the RYG status balls
I need help to automate the RYG status balls in the "Status" column, depending on the "% Complete" for each request. This is my formula but it kept showing "Red" though I have % Complete is 75% and 100% =IF([% Complete]@row < 70, "Red", IF([% Complete]@row > 70, "Yellow", IF([% Complete]@row = 100, "Green"))) Requirement:…
-
SUMIFS AND Formula Help
Hello, I am attempting to calculate the sum of a PRICE column, if the COLOR column=BLUE AND the FABRIC column=TW100. If I type =SUMIFS(PRICE, FABRIC, Fabric@row, COLOR, Color@row) it calculates the total for ALL rows in sheet including a BLUE color, even if the FABRIC is not TW100. I need a formula which will only…
-
How can I use IF to get a % in the topmost row in a hierarchy?
The issue is that of the 4 lines there are two options based on level of completeness. I've come up with this, but it's not working. =IF(Status4 = "In Process", "10%", IF(Status4 = "Complete", "20%", IF(Status5 = “In Process”, “30%”, IF(Status5 = “Complete”, “60%”, IF(Status6 = “In Process”, “70%”, IF(Status6 = “Complete”,…
-
Looking to create an =IF(CONTAINS Function for a formatted Date field
Hello! I'm looking to create an =IF(CONTAINS function to pull the day of the week into a separate column from the formatted date column. For example: I want the "Event Day" column to turn Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, or Sunday depending on what is in the Event Date column. I have created this…
-
formula question
we are using smartsheet to keep track of community park passes. i have a column for name, account #, tag # and pass #. I need to be notified / alerted if an account gets more than one pass. Is there a formula that can check for a duplicate account number when the pass # cell is not blank? In the below picture, I sorted by…
-
Unique Variable for JOIN function
Hi All, I am attempting to use CONTAINS in tandem with JOIN(COLLECT()) to populate courses employees must take based on the care-area in which they work. In the below example, the contents of 14A are also being pulled into 4A since they both contain "4A". Is there a way to search for 4A as an absolute? (Unfortunately, I am…
