-
Stumped on formula for Status of parent task with multiple children and criteria
Hi All, I'm trying to write a formula for which the parent task status is based on the following logic with regards to the status of the children: If all child tasks are “not started”, the parent task should be “not started” If all child tasks are “completed”, the parent task should be “completed” Any other combination…
-
Set dropdown based on values in row
Hello, I'm trying to set values in a dropdown list using a formula (or any other suggestions) based on the columns in the same row. For example, I have a drop down list titled Cities that allows multi select with the values Chicago, Boston, Atlanta as options. I have three columns on the same sheet named Chicago, Boston,…
-
Status is blank when "% Completion" column is less than 100%
I inherited a sheet with an existing formula to calculate the task status. It works just fine UNLESS the "% Completion" column is less than 100%. I am fully stumped how to fix it. Hoping one of the many Smart people here can help.. =IF(ISBLANK([Start Date]@row), "Not Planned", IF(AND([% Completion]@row = 0, [Start…
-
Counting Two different terms between dates
Hi, I am trying to count the number of times term "Incident" OR the term "Near Miss" appear between two dates, but my formula is giving me the incorrect count. The formula I am using is: =COUNTIFS({2. Initial Safety Review Range 2}, >=DATE(2023, 1, 1), {2. Initial Safety Review Range 2}, <=DATE(2023, 1, 31), {2. Initial…
-
Contact column to return multiple email addresses
Good morning, I have a contact column, then an email address column where I use a Vlookup function to go to another sheet and pull the users email address from. However, when I enter multiple contacts I want a formula to return ALL those email address back to my email address column. I know VLOOKUP isn't it, and I tried…
-
SumIfs with a Date
Once again I am having trouble with the SumIfs function. I am trying to get a total of postage paid for a client for each month, currently trying to find December 2022. I have the Customer Name column, Date Mailed column, and Postage Office Charge column. I have tried so many different formulas. Currently I have this:…
-
Can you use Cross sheet references to sheet in another domain?
I have project schedules in our Smartsheet environment. This works as expected. In some projects, we have large vendor partners that have their own schedules that they maintain IN THEIR OWN instance of Smartsheet. We would like to be able to collaborate more closely and link certain fields in MY project Smartsheet with the…
-
COUNTIFS Current Year
Looking to count those with a Project Status of closed for those with an End Date in the current year. My formula results in a #INVALID OPERATION. Can someone assist me with what I'm overlooking? =COUNTIFS([Project Status]:[Project Status] = "Closed", [End Date]:[End Date], IFERROR(YEAR(@cell), 0) = YEAR(TODAY())) Regards,…
-
Add Cell Data in a Custom Form URL
Hi. I've created a form and want to create QR codes that, when scanned, pre-fills certain data (a session title, and speaker name). The Session Title and speaker name are in a different table (TABLE1) for which the form feeds (TABLE2). The URL will ultimately resemble:…
-
Count formula needed
Trying to do a count formula that compare. So two columns, One called actual and the otheris call budget I want to do a count if Actual is Greater than budget then I want to do another count where Budget is greater than actual Thank you
