-
Index/Collect/Lookup not working
I've put together a function that pulls an autonumber value from a cross-reference. The problem is that the formula does not evaluate on all records. In the first snippet below the Row Record field does not return a value for all lookup values in the Base PO field even though I have confirmed that all the lookup values…
-
Health Status Formula
Hi! Currently, I have a formula in place to indicate R,Y,G Health based on the Start/Due Date. Is it possible to add an =OR function so that if "Status" column is switched to complete the Health will indicate green. Sorry .. very new to this.
-
If price above/below number of cents...
I'm new to SmartSheet and am struggling to create a formula for the following: If number ends in 0-29, round DOWN to nearest 0.95, (ex. FLOOR(A1,1)-0.05). Otherwise, if number ends in 30-99 cents, round UP to nearest 0.95 (ex. CEILING(A1,1)-0.05). ie: $59.29 rounds to $58.95. $59.30 rounds to $59.95 Can anyone help me put…
-
Slot scheduling conflict within a date range
Hello Smartsheet Masters, I am making a scheduler sheet for equipment slots. The sheet contains these columns: Conflict (Checkbox), Row (Dropdown list), Slot (Dropdown list, multi-select enabled), Start Date, End Date. I would like the Conflict cell to be automatically flagged if any one or more slots have already been…
-
How do I amend this formula
What should this formula look like if I also want it to ignore any tasks that do not have an end date? =IF(AND([End Date]8 < TODAY(), NOT(Status8 = "Complete")), 1, 0) Its template formula from a project template set that surfaces at risk tasks to the roll up dashboard. IE those where the end date is i the past and the…
-
Use Max Collect to find most recent record with value in multiselect columns
I have a sheet with 2 multi-select columns. The value options for the 2 columns are the same, and are listed in a 2nd sheet. In the second sheet, I'd like to write a formula that finds the most recent date where the value is present in one of the two multiselect columns. Once I have that data, I'd like to know which column…
-
Checked box applies formula to another row
Hi, Is there a way for when a box is checked to have the formula below automatically apply to the Health column? Basically, we only want Critical Path items that are checked for the below to apply to. =IF(Status@row = "Completed", "Blue", IF([Estimated Completion Date]@row >= TODAY(), "Green", IF([Estimated Completion…
-
Creating an At Risk Formula
Hello Community, I am trying to create a formula that will create a flag if the tasks are not marked as Completed in the check box column and the Due Date is within the next 3 days. I've made several attempts at using an IF/AND formula, but can't seem to get it to work properly. Any assistance you can provide would be…
-
Add the ability to enter simple formulas into cells in a Report View
I've got a Report View that simplifies what columns & rows are displayed out of the full sheet so that a user can input data on a weekly basis. We recently are facing a scenario where we need to sum up multiple values into a single cell. Although the math isn't 'hard' it is more efficient to simply type in i.e., '=123+456'…
-
#CALCULATING Error - Incorrectly Stating Over 100000 Cells Referenced
Good morning, I am receiving a #CALCULATING error for the below formula: =COUNTIFS({Mobile Unit Data Form Range 15}, =1, {Mobile Unit Data Form Range 3}, "EMPLOYEE", {Mobile Unit Data Form Range 1}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))) Range 15 is a checkbox, Range 3 is…
