Formulas and Functions
Archives for August 2019
Discussion List

First post 08/30/19,Last post 09/09/19Hi all, I am working with SS support but I thought I would reach out here as well According to T2 support ...If our server has not processed all data/ formulas before a save is made the sheet may show a different version than our server…Formulas and Functions282djpreece

First post 08/30/19,Last post 09/25/19Hi, I am having a problem with one formula that is not automatically copying to a new row (there are others that are being copied). The formula is: =JOIN(CHILDREN([Column Name]1, ", ") Why every other formula is being copied bu…Formulas and Functions269paulen

First post 08/30/19,Last post 09/03/19I am struggling to figure out how to have a "master" listing of customers that can be referenced by various sheets via INDEX/MATCH cross sheet formulas when that list is too large. Here is my background: I have a master custome…Formulas and Functions1047kgb2019

First post 08/30/19,Last post 08/30/19Is there any way to lock/restrict a cell that asks users to choose a date? For example, I want users to be able to choose a date earlier than what is "listed" in the cell but be unable to choose a date after the date that is lis…Formulas and Functions574Terrapin69

info.isaackwikcut92946 108 views 10 comments 0 points Most recent by [email protected] Formulas and FunctionsFirst post 08/30/19,Last post 09/04/19I am trying to get a formula to look up a value with the INDEX / MATCH function and the Search_Value being the numbers up to a "" dash from the "Type For List" column. The index/match does not seem to like the LEFT or…Formulas and Functions10810info.isaackwikcut92946

First post 08/30/19,Last post 08/30/19Hello, How do you generate a formula to change the text within a cell. I want to have different text for different due dates such as if the due date is within 5 days, I want the "status" cell to say immediate, and if it is withi…Formulas and Functions221KimN.

First post 08/30/19,Last post 08/30/19Hi =ROUND(SUMIF($[Pareto Category]$32:$[Pareto Category]558, Predecessors21, $[Actual (MHrs)]$32:$[Actual (MHrs)]558), 0) The above is my formula. It says, "IF the value in the Pareto Category column is the same as the value in th…Formulas and Functions271Bruce678

First post 08/30/19,Last post 09/16/19Hello, I spoke to Support on the phone yesterday but we haven't had a definitive answer on what the expected behaviour for this scenario is. I'm curious to see what other users have experienced. In the the "Edit or Remove links&qu…Formulas and Functions407Jaye Tatone

First post 08/30/19,Last post 08/30/19HI Smartsheet community, I need a formula that averages one column (# of jobs) if a checkbox is checked. I think I need to use AVERAGEIF and ISBOOLEAN, but I can't get the syntax right. Help! Bridget SloaneFormulas and Functions113Bridget Sloane

First post 08/30/19,Last post 09/01/19Hi, Is there any way to make the category field and the risk level field mandatory for users to complete before they can change the status from open to closed in my Smartsheet? Currently we have users who are just closing the status witho…Formulas and Functions22012sahilhq

First post 08/30/19,Last post 08/30/19When I sort rows, the formulas are not maintained: = SUMIF($[column_A]$7:$[column_A]237, "Received", [Column_B]$7:[Column_B]237)) gets transformed into this: = SUMIF($[column_A]$57:$[column_A]237, "Received", [Column…Formulas and Functions142Michaël @ NDA Group

First post 08/29/19,Last post 09/09/19I want to JOIN(COLLECT) unique, distinct instances of a date given values in other columns. I have a history log of participant registration dates. The participant can register more than one time on a given date. I want to JOIN(COLLECT) al…Formulas and Functions2128alex44

First post 08/29/19,Last post 09/03/19I am using the following formula (from a template) top calculate Working Days Remaining for a task  =IF(ISERROR(NETWORKDAYS(TODAY(), [End Date]@row)), "", NETWORKDAYS(TODAY(), [End Date]@row)) However, I want to add a condition…Formulas and Functions143stiss

First post 08/29/19,Last post 08/30/19Im close on a sumifs formula, but for some reason i cant quite get it. trying to count installations on a give date from a given list of properties Here's what i have. it's returning a unparceable response =SUMIFS({7900 Park Central Ins…Formulas and Functions121steve50951

First post 08/29/19,Last post 09/11/19I explain, I need to create an expiration date from a dropdown menu where the different types of payments of my clients are informed. Example, if a customer's account is "up to date" it means that the due date will be the issue …Formulas and Functions854Gerardo_Clarke

First post 08/29/19,Last post 08/30/19Hello All, I'm trying to get the sum of children. There are 4 children rows, with multiple rows beneath that. See image. In this case, the top row with cell value 0 would have a 2 because it's summing the purple 0 and purple 2. Purple 0 …Formulas and Functions678craigwaco

First post 08/29/19,Last post 08/30/19I am trying to find the total of Task Total Hours only for rows with a tier level of 2 and where % complete = 1. I am writing this in a summary cell in a minidashboard that requires me to write the formula in the % Complete column. I am t…Formulas and Functions252Ben Donahue

First post 08/29/19,Last post 08/29/19I need a solution to the vlookup max reference cell count of 25,000. I have a database that zapier loads data too from an application. The database consists of 134 columns, and will grow to an estimated 300+ row count. Which works out to …Formulas and Functions411@Diana

First post 08/29/19,Last post 08/30/19I am trying to created check point progress with check boxes and a visual aid. I created a If formula, but my progress is not adding up. I would like to know if anyone can help me with a better formula. (Image)Formulas and Functions122smcelvany

First post 08/29/19,Last post 09/01/19Hi, I have a sheet with several formulas, including a =vlookup on a text cell and =Join (). All these formulae autopopulate when a new row is added manually of via a form submission. However, I also have a contact column which, while suc…Formulas and Functions826IBRIEOG

First post 08/28/19,Last post 08/29/19I'm having issues with the following formula and I'm not sure why. Task Formula below: =IF(ISBLANK([Start Date]@row), "Blue", IF([Start Date]@row >= TODAY(), "Blue", IF(AND([At Risk]@row = 1, [End Date]@row <…Formulas and Functions233reynaldoloera

First post 08/28/19,Last post 09/04/19Hello All: I am having a difficult time creating a formula that will give me Priority. Below are my rules for Priority Status. Thanks Urgency High If Impact High and Urgency High, then Priority is 1 If Impact Med and Urgency High…Formulas and Functions8212AB

First post 08/28/19,Last post 08/30/19Hello! I am trying to count the number of requests types and exclude certain statuses. The current formula is: =COUNTIF({Request Tracker Range 4  Request Type}, Category13). I want to display the current number of Client or Internal req…Formulas and Functions171jmullennix

First post 08/28/19,Last post 09/06/19How do I make a row update the due date for a monthly, weekly, and/or quarterly recurring task? I would like it to automatically change the due date to one month, one week, or three months in the future and change the status back to Not St…Formulas and Functions813Arez

First post 08/28/19,Last post 09/09/19Anyone able to help with an Index/match formula that returns a wrong value? We've a formula that is returning a wrong value. I believe the formula is correct (it's the same way I write it in .xls). We are referencing another Smartsheet…Formulas and Functions1598kyle.macleod

First post 08/28/19,Last post 08/28/19Hello I am trying to build a formula where: * If the "Date Entered" column is within 60 days, the "Req Health" stays Green * If it is 90 days or more, Req Health turns Yellow (I got this formula figured out) * 125 days…Formulas and Functions502styless

First post 08/28/19,Last post 08/30/19Hello, I am trying to do a cross sheet formula that counts the number of tasks that are not complete and have a due date in the next 14 days for a specific department. I hope I am close but it is coming back as unparseable. Could some…Formulas and Functions452jbedoka

First post 08/28/19,Last post 09/10/19I have two columns, Column A and Column B. How can I check Column A for a condition, and then count all the distinct corresponding values in Column B for which that condition is met? Is there an easy way to do this with Countif / Countifs…Formulas and Functions29910Jeff S.

First post 08/28/19,Last post 09/12/19Dear, I appreciate your help. I need to create a formula that changes the status column depending on the following conditions. (The image in the example table has reference TODAY = August 28, 2019) 1) If the Balance column is equal to 0 …Formulas and Functions4019Gerardo_Clarke

First post 08/28/19,Last post 01/24/20Sheet 1: Work Scheduled (number of work units scheduled in a given month) Sheet 2: Budgeted Cost of Work Scheduled (to determine the total cost of the work scheduled in a given month) I would like to multiply a cell from Sheet 2 with the…Formulas and Functions81317JoeS