-
Column Formula for a future date, 3 months before annual start date
Hello everyone, I have a column formula that creates a date in the future based on [Start Date] while simultaneously avoiding child rows: =IF(AND(Hierarchy@row = 0), IF(MONTH([Start Date]@row) >3, DATE(YEAR([Start Date]@row) +1, MONTH([Start Date]@row) -3, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row), 12 +…
-
SUMIF(S), AND, IF, Checkbox formula functions
Hi brainstrust, I am trying to build a resource conflict formula(s) that deliver the end results of: If a project row has overlapping dates AND has the same resource type(s) selected (different columns with check boxes) as the date-conflict project rows, return a "conflict" result. AND If a project has a resource type(s)…
-
ACOS Function does not exist
Hello, I have created a Smartsheet to collect operations data and I'm now trying to add a formula in there to automatically return a result using a formula. In other words, cell B1 and C1 get filled out through a form, and when that happens I want D1 to show a result using a formula which includes ACOS (Arccosine) and SQRT…
-
Row referencing while cross-referencing sheets
This is my current formula in order to input the proper value from my master sheet. I am trying to change it so that I can drag the formula down (similar to Excel) and have the value of 1 update to 2 and so on. From what I have read it seems that the @row function is the answer to this problem but when I use the equation…
-
CountIF Series of Checkboxes, up to last unchecked box
Hello, I have a sheet with a series of 6 check boxes for different tasks. A team-member checks a box when an operation/task is complete. Some tasks can be done concurrently and are not dependent on another, but I would like to indicate the last completed task up until the next blank check box. Example 1: Checkboxes 1-4 are…
-
Return latest value, by date, if criteria match
My brain is absolutely melting over this one. I'm not sure if what I'm looking for is too involved, or if I am just truly not understanding how to piece these formulas together (the latter is 100% likely the case). I'm looking to do the following in a Summary field: Step 1: Return the latest value from Supplier based on…
-
Formula returning unique values not working
Hi, I am using the following formula to return unique values in a spreadsheet. It works for other columns but not a specific column. =IFERROR(INDEX(DISTINCT({WDRS Core Build Log Range 3}), IDX@row), "") IDX is a helper column and this formula works for other columns. This specific column contains release numbers in the…
-
IF(CONTAINS) #UNPARSEABLE
Hello, I would like to return vendor details using VLOOKUP function looking up per vendor only if service contact is available. In addition, if there is no vendor found on the reference page, text "Add vendor to Vendor List" should appear. Tried this formula and received error message #UNPARSEABLE =IF(CONTAINS(“Yes”,…
-
Sheet summary to populate date in sheet
Hi all, I'm currently creating a template to monitor store closures and need some support in setting up a way to populate a date from the sheet summary. The end goal is to when in use, to be able to populate the date for the activity to start and able to flag any outstanding points after 30 days. We are able to populate…
-
Updating Status Based on RYGG symbols of Children Rows and RYGG Formula
New day, new formula conundrums! For these formulas, I'm looking to update the status of my PARENT rows (2 levels) based on the RYGG symbol as well as having the RYGG symbol is based the CHILDREN rows' Task Status column. I have both working but I feel like they could be cleaner and just wanted to get a second set of eyes…
