-
Need to formulate long date imported from form into a reportable date in Smartsheet
I have a form (not Smartsheet and unable to convert to Smartsheet form) that populates into Smartsheet. Dates come in long form (I.E. September 30, 2019), which means they are not reportable in Smartsheet. I need to get the long form date into a date format Smartsheet recognizes so I can run reports off of it. I have tried…
-
How do I add text to the end of a formula?
I have a formula that calculates the number of days and would like to add the word "days" at the end of the formula. In Excel, I would use '& "days"' as in =NETDAYS(Added1, Closed1)&" days" ...
-
Red Flag If statement
I have an if statement that is doing what I need except when there is no value in the deadline column. I want to keep the functionality I have but make sure the flag is not red when I have no deadline set. =IF(AND(Deadline2 < TODAY(2), Completed3 = 0), 1, 0)
-
What else but formula help!
I need a formula for my sheet and I am failing. I need a formula that works for the attached screenshot. Calculates: Qty (LB1) x List Price (LB1) x % (LB 1) I really should have paid more attention in algebra! :-(
-
Joining cell's with text contents and checkboxes selections onto one text string
We have a design queue sheet where we receive design requests via a web form. Users make a scope selection using a couple of drop down menus and a bunch of check boxes, and an "other" text box for miscellaneous tasks. On the queue side, the number of columns where this information is gathered occupies quite a large area of…
-
Multilevel Collect/Sumifs Formula Difficulty
I am attempting to parse a large amount of sheets together and have hit a snag in a formula. My data looks like below: H | A | B | C 1 | 7 | 4 | 9 0 | 2 | 1 | 5 1 | 5 | 3 | 1 H is a helper column displaying a 1 if the data should be utilized and a 0 if not. I need to reference a range as a whole in order to keep my number…
-
Averages with Errors
Hello, I'm trying to take an average of a range of cells. Some of these cells have a #Divide By Zero error in them because the source sheet does not yet have applicable data. I have used an IFERROR statement to change these cells into "blank" cells, yet when I use the following formula: =AVERAGE([Avg. Functionality]1:[Avg.…
-
Text in Date Column Messing Up Formula
I have 8 date columns where sometimes I enter text. Either the date of a document's expiration, or if the document was simply "received." I have another helper column that I want to be checked off if any of the 8 dates are expired. BUT I'm finding if I have text in any one of them, the formula becomes "invalid operation."…
-
RGY Status - Overall Roll up
Hello helpful Smartsheet-ers! I have several rows that are utilizing the RGY buttons for health. I am trying to roll these up to a general status based on the choices in the individual rows. Can anyone help with a single formula that would capture: Green if all elements (rows) are green Yellow if one or more elements are…
-
RYG Balls changing with Children Rows
Hello, I have an *awesome* formula that changes RYG balls of parent rows as the children rows change. The formula is below: =IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(),…