-
Help with multiple OR
I am at a loss and getting an unparseable error with the below formula. I have several criteria which I am trying to satisfy with the OR function. =COUNTIFS({Role},"Operator", {SnowShift}, "Night", {Available},"Available", OR({Dept}, @cell ="Airport Field Maintenance", @cell = “Airport Facility Maintenance”, @cell =…
-
Circular Reference with Vlookup (Works in Excel)
I have a project management table I was hoping to make in Smartsheet. Basically, if a task has a "Predecessor", I'd like the "Start" for that task to be the end of the Predecessor task (whichever task has a matching "Task ID"). If a task doesn't have a Predecessor, I'd like its Start date to be the End date of whatever in…
-
Formulas
Good Afternoon... We are building a sheet to include a column must have 1 of 4 different criteria can be met as well as a True/False criteria formula. When completing the form, the fields are not populating correctly and if you copy the formula down the column, it puts False in each field. What is the best way to get…
-
Date (mm/dd/yyyy) from Week number
I am trying to get the date displayed from a week number. For instance this week is the 27th week of the year. I am trying to get it to display 07/05/2021 (the first day in the week). I was thinking of converting the week to day of the year (i.e. 27 * 7 = 189) and using the 189 to convert to a date, but I can't figure out…
-
Cross-Referencing Assigned To Error
I am building out a roll-up sheet and have everything working nicely, with the exception of a single formula. The part of this sheet I'm having trouble with is in the image below. What I am trying to do is have this sheet look at the person named in the column "Primary", then check a couple other sheets to see how many…
-
How do I Check/Uncheck Parent if at least 1 Child is checked?
I have been using this formula: =IF(COUNTIFS(CHILDREN(), <>0), 1, 0) but if more that one child is checked in the column it comes up with #INVALID DATA TYPE . How can i fix this. The requirement is at least one Child row must be checked for parent row to be true if not false.
-
INDEX MATCH needs to pull blank if there's nothing in the cell
I'm currently writing a formula to pull the data from one sheet to another. My formula pulls the data in if there's information in the cell. If there's nothing in the cell it returns "#NO MATCH". How do I get it to return nothing if there's nothing there instead of NO MATCH? Here's my current formula- =(INDEX({RPA Last…
-
+1 for phone numbers formula problems
I have a column for phone numbers that come in from the form and they come in with the +1 format. in the column right next to it I'm trying to write a formula so that if the phone is blank it gives N/A. =IF([Mobile Phone]@row > 0, "[Mobile Phone]@row", "N/A") I know my problem is the + sign because if I remove it the…
-
Formula HELP!
I know this is example is from excel but I'm wanting to use this in smartsheet. I need formula help! In the chart below I need a formula that will look at the information and display them in numerical order and ignore the blanks. So example would be if B2=1 then F2=1 and if C2 is blank but D2 is 1 than G2=1. I want them to…
-
Is there a way to conditionally copy the value of one cell to another?
I am trying to create a modified auto-numbering scheme where the numbering is based on a parent/child relationship. I think I am on the right track, but have hit a roadblock. My end result should be a calculated ID that is not overwritten when IDCalc changes. I would like the value of IDCalc to populate in the ID column…