-
Expiry Dates - Compliance Check
Dear SmartSheet Community, I need your help. I need help in writing a formula that will track compliance for different areas based on expiration dates. If dates are active and will not expire in the next 30 days, health should be green. However, if one or more items in the row will expire in 30 days, yellow and anything…
-
How to reference another sheet in a formula
I've been trying to write a formula that will "Check a checkbox (in sheet A) IF [name] cell (in sheet A) is found in {name} column (in sheet B), AND the box in [checkbox] column (in sheet B) is checked." Basically, does a particular staff member's name appear on another sheet, and if so does it have a checkbox checked in…
-
Behind/Ahead of Schedule by "x" days
Hi All, I'd like to add a column that reflects how many days my project plan is ahead/behind schedule. I asked this question last year, but couldn't find it anywhere. I have a Planned Finish and a Finish Date. I'd like the row to display the following for each: If no variance, display "On Time" If late, display " X days…
-
Health check with 3 criteria not working
Hi All, it's probably something REALLY basic for you all, but I cannot for the life of me get my Health check status to convert from Red"No"/Yellow"Hold"/Green"Yes". I have written my formula as: =IF(ISDATE([LOA Issued]@row), "No", IF(AND([LOA Received]@row = 1) + (ISDATE([LOA Issued]@row)), "Hold", IF(AND([DRAs]@row = 1)…
-
Easy Date Question: Today plus 30 but nothing in the past
This one should be easy but every iteration that I've tried is not working. I would like the formula to first check the Expired column to see if it's checked, if it is, return "Closed", if it's not, check to see if it's today's date or 30 days out and if it is, return "Blue" (I would like to see any tasks that are within a…
-
Calculate Average if another Range meets criteria
Hello, Any suggestions on the best way to calculate the average days if the item type is equal to quarterly? I have a drop-down menu that categorizes the activities based on frequency, and another column that calculates the days past based on end and start dates. How could I find the average if the Item type is Quarterly?
-
CountIF w/ multi select
I'm trying to create a formula to count the number of rows containing a specific location where the location column is a multi select. Also, part of that formula needs to only account for that location in an "Open" status. So far I've been able to make the formula work to disregard anything that is "Complete" or "On Hold".…
-
Multiple IF statements that require the formula to ignore a blank cell
Sorry for the 2nd post; however the answer provided worked "somewhat". I am trying to create a formula that will look at a date and determine if today's date is greater than the date in a particular field it will bring back the word "expired" but if all the dates are greater than today's date it should be left blank; The…
-
Help with a SUMIF formula 🙋♂️
I am having trouble with a SUMIF formula that is intended to calculate "returns" in the shipping/inventory database below: My intention is that when a number is entered in the "Spare Qty" column underneath the "Returned" cell in the blue parent row for a specific SKU, that the "Returned" column in the far right…
-
Copying rows
Hi, Is there a way in Smartsheet that once rows a copied from one sheet to another using the "Copy rows to another sheet" automation that they move under set sub-heading instead of directly to the bottom of the sheet? I have attached an example below of one my sheets with this scenario. Would it be possible to move the…
