-
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…
-
Index Match for Multiple Options
I want to set a formula that will index match the value(s) of a cell with a reference list of group name and associated contact information. Is this even possible? I have Sheet 1: list where each row has an entry that states in one cell, multiple group names I have Sheet 2: list of contacts by group name I want to have…
-
Moving children rows under a different parent row
Is there a way to set up a formula that will move project status "completed" from under one parent row to another parent row named "Completed project" once the project status has been selected as completed?
-
Formula for "Due date = 3 business days before Deploy date?"
Hi, I would like to build a formula with the following logic: "Due Date = 3 BUSINESS DAYS prior to Deploy Date." Building a "3 DAYS prior formula is simple enough, but I'm wondering if anyone has solved for logic that can only offer a day that's Monday thru Friday. I appreciate any advice! -Adam