-
Have Children Status based on due date but have parent roll up based on all of the children status
Hey All, I am having some difficulty getting a formula to work the way I want to here. I want all children rows to look at the due date column and if it is overdue, mark the status as "Red". If todays date is within 3 days, mark the status as "Yellow". If todays date is over 3 days, make it as "Green". I just want these…
-
How to set up If Then formula that includes date values
I have been trying to create a formula that uses two data points that will have a different status based on if it is before or after it's need by date. This is what I currently have: =IF(([Quantity Ordered]<>[Quantity Shipped],[Quantity NBD]>(today), "Open")) I am not sure what is making it so that the formula does not…
-
Custom Formulas or build in formulas for symbol to value conversion
We are currently evaluation Smartsheets (and others) for our team. Smartsheets feels very intuitive. The one thing I like are the symbols for completion e.g. the bar or the pie symbol, but then I'm struggling to use those in formulas. E.g. I use the bar symbol but now want to get to the overall progress for a parent task.…
-
I am trying to get the highlighted formula to exclude weekend days.
The way the sheet is currently setup, each row is a different job. there are no parent or child lines. I am trying to set a milestone date from one department task to another. However, the formula doesn't seem to recognize or account for when dates fall on a weekend. 1/6/24 and 1/7/24 are both weekend days. How can i write…
-
Is it possible to include a formula in a hidden field in a form?
The reason for asking is we are creating a smartsheet with form for training candidates to register their names, which will then create a certificate using document generator. In order to get the certificate PDFs to have a unique file name I have added a column called file name with the formula ="COSHH Training Certificate…
-
#UNPARSEABLE Sumifs
My sumif formula keeps coming up unparseable and i can't figure out why. I'm not even getting the highlighted frame for when I identify the column I want to be summed. What am I doing wrong? =SUMIFS([# of Car Seats]:[# of Car Seats], [Date Distributed]:[Date Distributed], AND(@cell >= DATE(2023, 10, 1), AND(@cell <=…
-
Calculating a Duplicate if meeting 2 separate criteria
Hi, I'm currently calculating if something is a duplicate by using the following formula: =IF(AND([PASS NUMBER]@row <> "", COUNTIFS([PASS NUMBER]:[PASS NUMBER], [PASS NUMBER]@row) > 1), "Duplicate", "Not a Duplicate") However, I now intend to expand the use of this sheet, so would like to include another column to specify…
-
Index Function Has Stopped Working
Has anyone else experienced this as of 2/19/24? In Control Center, many of my sheets that use Index have suddenly had the formula fail to work. These are sheets where they were previously working with no problem. Troubleshooting eliminated the idea the problem was with Match (correctly returns the row index), but even when…
-
Request help using INDEX MATCH
I'm trying to identify conflicting dates and locations for assets. The assets are entered into the sheet as new rows from a form. There are two columns: [Scheduled Arrival Date] and [Arrival Wet Slip]. I'm trying to use INDEX MATCH to identify when an asset's [Scheduled Arrival Date] and [Arrival Wet Slip] conflict with…
-
Reduce clicking, allow user to edit all sheet and sheet summary formulas at once
I feel like I'm clicking endlessly... 👆️👆️🖱️Double-click to open the cell formula 👆️👆️🖱️Double-click to highlight the part that I need to edit 👆️👆️🖱️Double-click to open and edit the next cell... My hand hurts. It would be great to have some sort of edit pane that allows me to edit all formulas on a sheet at once. Or…
