-
IF Error Function help
Hello, Request your help to figure out what I may be doing wrong here. Please see the screen shot below The formula is on the % complete column below. I want the #DIVIDE BY ZERO to be a NULL value and was trying to use the formula below . The formula gets the desired result but I want the I want the #DIVIDE BY ZERO to be a…
-
Automatically Organizing Rows into Hierarchy
Hello Smartsheet Community, I am yet another formula-deficient SS user. Looking for insight on a formula or function I can set up, as I asked the AI formula developer tool to create a formula for this request and it stated, "Smartsheet formulas cannot be used to automatically organize rows into hierarchy". I have created a…
-
Creating formula with multiple items selected
I have a sheet that has a field that allows multiple items to be in the field. I want to create a formula that will allow me to count the row if even one of the items in the row matches my criteria but it's only counting the row if there aren't multiple items in the field.
-
Adding Multiple Criteria to a COLLECT Function
I have a working formula gathering the MEDIAN of a group of numbers that fall within a date range used in a trend chart in a dashboard. I now want to add two additional criteria for cases when the dashboard user would like to calculate the MEDIAN but exclude certain values (referred to as 'HOLD' and 'Strategic' data) from…
-
Lock Column Settings
Hello all!!! Quick question I feel like I know the answer to. But is it possible to change a lock column setting so the column locks after a condition is met? For my example a column to only lock after a status is changed to a certain value.
-
IF AND Formula with Multiple Criteria and return Text Value
I have a percentage completion column, status column, and finish date column. I would like a formula as follows to enter a text value into the status column… IF percentage = 100 and Finish date = Today then enter "Complete" if true and "In Progress" if not. If percentage > 100 and Finish date > Today then enter "Not…
-
What is the function of number one at the end of this formula?
=INDEX(COLLECT({Column to return}, {Column 1 with value to match}, "Value 1", {Column 2 with value to match}, "Value 2"), 1) Thank you!
-
leading with "0" but the cell must be a number
I am trying to formulate a row so that it always contains three numbers, starting with 0. This column has to be a number and not a text as I need to sum the total in a report. Currently, I am only able to make sure there are three digits by making it a "text" cell but when I go to summarize the total in a report its not…
-
Index match formula for the month
Hi Everyone I have two sheets here "Intake" & "CalRef". On the intake sheet i have the column - Date month funded month Nr which return the number month from the column - Date funded. Now i want the use the formula: =IFERROR(INDEX({CalRef Range - Month Name}; MATCH([Date funded Month Nr]@row; {CalRef Range - Month Nr};…
-
Formula for % complete is missing a rule
I have this formula which is working. However what it is not doing is picking up a Not started task sitting at 0% that it is overdue. It only marks it as overdue once I move the task to an in- progress state. =IF([Percent Complete]@row = 1, "Complete", IF([Percent Complete]@row = 0, "Not Started", IF(AND([Percent…
