-
Formula for impact score x likelihood
Hi i currently have the below formula but need help amending this =IF([Impact Score]@row = "", "", [Impact Score]@row * [Likelihood Score]@row I need to amend this for if column type is risk then impact score* likelihood score and if column type is issue then return impact score only Thanks
-
Help with addition to formula
Hi, i need to make an addition to the below formula please. I need to add if column type is issue and impact score is 4 - gray, 3 - red, 2, amber and 1 green =IF(AND(Type@row = "Risk", ([Impact Score]@row * [Likelihood Score]@row ) >= 20), "Gray", IF(AND(Type@row = "Risk", ([Impact Score]@row * [Likelihood Score]@row ) >=…
-
Multiple if statements with And and Ors
I have multiple Ifs and I seem to have tied myself in a knot. =IF(NOT(ISDATE([Earliest Day 180]@row )), "Red"), IF(AND([Send HIE]@row = "Green", ePrescribe@row = "Green", [Receive & Reconcile]@row = "Green", [Provide Patient Access]@row = "Green"), OR(PHCDE@row <> "Green", [Query PDMP]@row <> "Green"), "Blue"),…
-
IF Statements with Dates
Hi all, I am clearly missing something… I have a sheet with columns for the Start and Date Ends and then another column to track the Status (In Progress, Enrolled/Upcoming, Completed). The formula is currently: =IF([Start Date]@row > TODAY(), "Enrolled", IF([Start Date]@row < TODAY(), "In Progress", IF([End Date]@row <…
-
Formula Help
I am seeking assistance with a specific formula. I am creating an employee snow deactivation request and tracker. Employees can schedule time off during winter operations. The issue is that we have two periods, Preseson and In-Season. The Preseason period to submit your vacation is 7/15/25 thru 8/15/25 and In-Season is…
-
Multiple Condition Duplicate Line
Hello, I am creating a tracking system for PO's that need to be entered into a system. The problem is that a PO can be split into multiple truckloads and delivered to the facility on mutliple dates. The facility enters each PO that is needed based on a schedule but if the PO had been received previously, they do not want…
-
=IF(AND(OR(HAS Column Formula with 3 results (option 1, option 2, both) #INCORRECT ARGUMENT SET
I have a search range which is a column that has up to 35 bus routes. The bus routes are operated out of one of two facilities, "ARCADIA" and "POMONA". I am trying to create a column formula that will return a result of "ARCADIA", "POMONA", or "BOTH". I can successfully create a column formula that will return one of those…
-
If And Formula including dates and isblank
Hello, I am trying to create a formula that will flag the column Delivered Out, if the On Hand Inventory is equal to 0 and the Date Delivered is today or in the past. Both conditions must be met so if On Hand Inventory or Date Delivered Out is blank, it does not flag Delivered Out.
-
If Function for Drop Down
Hi Im looking for a formula in my sheet where if i select a drop down in one column it will give me a selection of drop downs in another column eg column 1 - if Commerce is selected column 2 - commerce, short breaks (drop downs) only eg column 1 - if guest is selected eg column 2 - guest, guest data drop downs only thanks
-
Multiple IF Statements Using "ISDATE"
I have an existing formula that enters text based on two criteria: if a date exists in a column, and if that item was closed prior to the anticipated delivery date. The current formula is: =IF(ISDATE([Closed Date]1), IF([Delivery Date]1 > [Closed Date]1, "Complete", "Fail")) I'd like to add an additional criterion that…
-
Formula for RAG rating based on status and dates
Hi Smartsheet community, I'm after a formula that will produce the different RAG ratings. I'm definining them based on whether they are on schedule and their statuses. Blue is 'Status'="Complete" Red is (open for advisory) 'Status'='Not started' and 'start date' < today and end date is within 14 days OR Status= not started…
-
Issue with task health formula
Hi all, I'm defining a column that will help PM's easily see which rows in a very large timeline to focus on (Health is the column name). I typed this directly into SmartSheet to avoid any invisible characters. I've looked at it many times and can't see an issue with the formula. Can anyone else see it? Based on columns…
-
help with formula IFERROR
Hi I need help with the below formula please as getting errors I would like to count requests submitted from Janaury - December in 2024 and 2025 from a specific region eg, europe, Asia etc
-
I need a modification on an IF THEN statmenet
I have a column in my grid that generates the Task number automatically for Level 2 and all of the children below it. IE if I choose the drop down "Task 1" for a row that is level 2 then that row and all children of that row will show "Task 2". I did that using this formula: =IF(Level@row = 2, LEFT([Task #]@row, 7),…
-
IF Formula
Hi I would like help on a formula to score time criticality. Ive tried the below but its coming up with errors. I also would like to add a formula that if there are any negative numbers such as -201 then this would show as "10" Formula i used below but not working =IF([Time Criticality]@row <= 30, "10", IF([Time…
-
Hi team, may I have help with the IF formula below?
=IF([Rank]@row = "1", [Request Date]@row + 5,""), IF([Rank]@row = "2", [Request Date]@row + 7,""), IF([Rank]@row = "3", [Request Date]@row + 9,""), IF([Rank]@row = "4", [Request Date]@row + 11,""), IF([Rank]@row = "#NO MATCH", [Request Date]@row + 11, ""))))) I'm trying to add an IF formula based on the rank column and…
-
IF AND Formula with Multiple Criteria
Hi there, I've searched so many threads and tried parsing together how this would work for my scenario but I keep getting an invalid argument. I have a column for "Status" and I want to incorporate a formula to auto select the status based on other cell criteria: Open: If "Date Shipped to Supplier" is blank At Supplier: If…
-
How to use IF/AND/CONTAINS formulas with multiple sheets
Hello, I am having a bit of difficulty expanding an IF formula to include a value that is in another column. Let me give an example In this mock workspace I have created basically what I'm doing and what I would like to happen. (all names and ID numbers in this workspace are fictional) In the Training Submissions Sheet, my…
-
Adding One More Criteria to Working COLLECT Formula
I have a working formula where I need to add one more criteria to a COLLECT function but can't seem to get it working. Here is the current working formula: =MEDIAN(COLLECT({Platform Test - Submit Status to Intake Date}, {Platform Test - Quote Testing Completed Date}, AND(@cell >= $[Start Date]$1, @cell <= $[End Date]$1),…
-
IF AND OR Statements
Hello! The sheet on the back end has multiple checkmarks to select what role the event is for. I am needing to create a formula that will give a Academy name based off of which checkmarks are checked. The column for academy name is a dropdown column. i.e. If support office is checked support office academy is put in the…