-
Help combining: IF INDEX MATCH
Hi, Please help me with my formula, I'm trying to GET a Contact Column, based on matching criteria, (The columns referenced are from a separate sheet.) I tried using: =IF({Sheet 1 Range Status Column}, "Approved", INDEX({Sheet 1 Range Contact Column}, MATCH(HAS(@cell, Reason@row), {Sheet 1 Range Reason Column}, 0))) It's…
-
Getting NETWORKDAYS to Work with Due Date Comparisons
Hello everyone, I am using Smartsheet to capture a bunch of data for vendor project tracking. I'm adding a few helper columns to compare some dates so I can evaluate performance and trends. However, I seem to be having trouble with the data for one of these helper columns so hoping to share what I've got to see how I've…
-
Text to Text Formula Help
Good morning, I'm looking for a little assistance. I want to create a formula that will change text in one cell if the word is in another cell. For instance, if I have DOG 246 in one cell, I would like the other cell to input DOG, however if the first cell has CAT 123, I want the other cell to input CAT. Any assistance…
-
Keep Health Status Formula When New Row Created
I am really close getting my health status formula that is based on both Status and Date to work. I need the formula to stay when a new row is added. I also want the status to be GREEN with DUE DATE = TODAY. Here is what I am trying to have happen If the DUE DATE is in the past turn the health symbol "Red" If the DUE DATE…
-
What formulas should I use to count two criteria (including from a multiple dropdown option)?
I want to count "Enrolled" from "Case Status" AND "Medicaid" from "Insurance Type." I used the COUNTIF formula, but it seems like Smartsheet isn't counting the Medicaid cell if it's within a cell with multiple criterias selected. Screenshots below: You can see that even though 2 fit the criteria, the sheet summary only…
-
Calculate percentage of cells complying to one criteria
Hi, I need to calculate the percentage of cells within a certain time frame that comply to a certain criteria. More specifically, I need to calculate the % of complaints closed in the past 28 days (Date of closure = TODAY -28) that have a complaint investigation duration of less than 15 days. Please find an example of the…
-
Check Box with multiple criteria
Hello - I am trying to get a checked box: If Column A value = Column B value, check box. However, I am also getting checked boxes when A and B are both blank which should not happen. Current formula is: =IF(AND([TRACcess Serial Number as Per TRACcess Manager]@row = [TRACcess Serial Number As Reported by Tech]@row), 1, 0)…
-
Formula returning 0% instead of correct value in the sheet...
Not sure why my formula (using Index and Match) is returing 0% as a result of matching to another sheet? The correct data in the cell is = 55%. All other columns with same formula working correctly but one dept is showing an error o= 0%. Here is the formula =INDEX({Feb Score Column - BEAUTY}, MATCH(UII@row, {UII Store -…
-
Parent/Child Hierarchy Formulas
Hi, I have a formula to figure out where I want the parent rows to increase by 1 each time, while the child rows maintain the same number as the parent. IE only the parent rows should increase to match the numbering convention. I'm trying to get the A.X to match what's in the right most column (as those will be deleted and…
-
Calculating Time in HH:MM:SS
Hello, I'm hoping someone might be able to help me with calculating time in Smartsheet. We work with production teams in broadcasting, as well as other non-production teams, so calculating time is becoming rather vital, as more and more departments are using Smartsheet for a varying number of use cases: people and resource…
