-
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…
-
Autonumber generated only for rows in Column A that are blank
Hello, I am trying to figure out a solution and would love some help. I have Column A that contains a unique employee ID number for each employee. However, I want to include potential employees in Column A as well, and they will not have an employee ID number (i.e., Column A will be blank). I want an auto-number to…
-
How to reverse order of strings in a column separated by delimeter
I have a column named Country with some values in it separated by the delimeter "|", and i want to revere the order of it. Eg: The country column has all the below values in a single row separated by delimeter "|" Australia | Chile | puerto rico | dominican republic | India | Kenya and i want to reverse the order of these…
-
Zero Formula
Where do I find the formula to have a cell display nothing when the total becomes the number zero? I have a sheet with zeros in multiple places and it clutters to much.
-
Allow Successors() to show only immediate successors
I like having a "successors" column to complement the "predecessors" column - I use this to trace forward to find tasks that are affected if I make a change. However, the successors() function returns not only immediate successors, but every task that is a child of those successors. While this may be technically a true…
-
confusing formula results.
I'm working in a smartsheet that combines data from other smartsheets using vlookup formulae. I've written a column formula to add the values in two of these vlookup columns and in some of the cells I get the expected result, and in some I get a #NO MATCH error. I can't figure out what is wrong here. I've reviewed the data…