Formulas and Functions
Archives for September 2018
Discussion List

First post 09/30/18,Last post 09/30/18I've tried both... they don't work they say unparseable... what am i missing I want to I am looking to add up all P1's that have a region of America Break Fix and a status that is not finished or cancelled, then count all the P1's in the…Formulas and Functions291Debbie J

dave.mcpherson56751 337 views 8 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 09/28/18,Last post 10/02/18Hi, i am hoping this is possible and someone can help? We have created a sheet which is populated from a form and is working really well. We populate the "Required completion date" base on the type of issue and this is automated…Formulas and Functions3378dave.mcpherson56751

First post 09/28/18,Last post 10/01/18Please can somebody help me, I'm new to formulas! I'm trying to get text in a cell to change depending on what decision symbol has bee selected (YES, NO, HOLD) I managed to get it working with just 'YES' as follows: =IF(Graphic6 = "…Formulas and Functions514[email protected]

First post 09/27/18,Last post 09/27/18I'd like to use Smartsheet to track performance on a multitude of projects. I can use a Report to quickly gather a list of assignments and compare Need Date to Date Completed. But I can't add any additional columns (i.e. with formulas t…Formulas and Functions131JeffG

First post 09/27/18,Last post 09/28/18Hello All, I got my function to work the way I wanted it to, but it did not keep the hyperlinks when it gave me the values. Is there a way to keep the hyperlinks or add them back when using Join(Collect())? =JOIN(COLLECT({Sandbox  DR  …Formulas and Functions155Nick Hosein

dave.mcpherson56751 2.8K views 15 comments 0 points Most recent by Genevieve P Formulas and FunctionsFirst post 09/27/18,Last post 04/02/20Hi, We have created a form to capture feedback from several stores we manage. How can i get the Store managers name to auto populate on my sheet based on the store name? I dont want this on the form just on my sheet when the entry has be…Formulas and Functions282915dave.mcpherson56751

First post 09/26/18,Last post 09/26/18I've looked at the forums and tried as many variations in the formula as I can think of, so I need some help. My two columns (both drop downs): Payee Name Payee Position *(location of formula) I'm trying to get Payee Position to autom…Formulas and Functions113Stephanie Hadley

First post 09/26/18,Last post 09/26/18Hello  I am trying to make my checkbox column automated so that when the PARENT rows is checked all the CHILD rows get checked. I can only find help on the reverse. Can someone help me write that formula? Thank you ErinFormulas and Functions23epollard

First post 09/25/18,Last post 01/08/20I would like to create a SUMIF equation to only sum data (column F) based on a date range (column B). It works in excel with the following equation for reference. =SUMIF($B$9:$B$2000, ">=9/1/2018", $F$9:$F$2000)SUMIF($B$9:$…Formulas and Functions60311dchapman

First post 09/25/18,Last post 09/26/18I'm trying to have smart sheets calculate a new date. I'm using =[EndDate]5+1 but it's not returning a date. What am I missing?Formulas and Functions254ceej

First post 09/24/18,Last post 09/25/18Hi, I am trying to extend a formula to look at a second Criterion in a spreadsheet. the current formula is ="NSW (" + COUNTIF(CHILDREN(State6:State61), "NSW") + "), QLD (" + COUNTIF(CHILDREN(State6:State61)…Formulas and Functions23Cap

First post 09/22/18,Last post 09/24/18I have a 10 digit "Store #". We use the right 5 digits as a "Site ID". I did a "Right" function to grab the 5 right digits. My Index/Match formula returned a #No Match error. So I copied & pasted values…Formulas and Functions982Pam Bates

First post 09/22/18,Last post 09/23/18=SUMIF(Date past due: Date past due,>TODAY(), Amount:Amount) #unparsable The amount is coming from a vlookup from another smartsheet The past due date is a formula with in the same sheet (Image)Formulas and Functions112Debbie J

First post 09/22/18,Last post 09/22/18Hi, I'm hoping smartsheet community can help me with an automatic formula. We have a column called "Priority Deal" this is a symbol selection of the stars, where we rate a specific land opportunity (empty/one/two/three/fo…Formulas and Functions134Stuart.d

First post 09/22/18,Last post 09/24/18Is there a way to return the numerical value of a cell that contains a formula that is counting the number of checked boxes in its children cells? My current formula in the cell is: ="Complete: " + SUM(COUNTIF(CHILDREN(), 1) + C…Formulas and Functions482Zyang

First post 09/21/18,Last post 10/01/18I have a sheet that totals the row values when a row is added from a form. It worked well until the cell containing the formula was linked using vlookup to a separate sheet that is the source of a dashboard. Now it will not calculate and…Formulas and Functions391Melissa Campbell

andrew.guirguis61991 1 view 1 comment 0 points Most recent by J. Craig Williams Formulas and FunctionsFirst post 09/21/18,Last post 09/21/18I want to create a formula that has a checkbox that will check if the due date column is between today and 7 days. It works but when i format it to the entire column it checks off every because because it considers that if the due date col…Formulas and Functions11andrew.guirguis61991

First post 09/21/18,Last post 09/21/18For a project release, we're leveraging checkboxes to track completion of validation tasks. I have a dashboard built to reflect progress, however am having trouble finding a way to only choose the multiple Parent Rows/Roll Up data, as it's…Formulas and Functions1345Rick_Bailey

First post 09/21/18,Last post 09/21/18Hi Can anyone help me with these two formulas I am trying to do: 1. IF a cell with a checkbox is true, then run this VLOOKUP formula, otherwise 0. 2. IF a cell contains this text, run this VLOOKUP formula, or IF the same cell contains t…Formulas and Functions2705Richard

First post 09/20/18,Last post 10/10/18I have looked through and tried to apply several solutions and none seem to work with a range of cells. I have a date/time field column under and have entries in several different collapsible fields. I am in need of help creating a form…Formulas and Functions2218ateleoh

First post 09/20/18,Last post 10/01/18Sorry to bother with such a simple question, but what is the problem with this? =IF(OR([Average 6mths]7 * 1.5 > [Current Monthly Usage]7, [ Average 6mths]7 * 0.5 < [Current Monthly Usage]7), 1, 0) I am just trying to return 1 if be…Formulas and Functions142michaeld

First post 09/20/18,Last post 10/14/19I'm trying to find a formula that can be placed in a parent check box cell, so that when ALL child checkboxes are filled in, the parent check box then becomes checked. I have multiple columns this is needed for, so ideally looking for a s…Formulas and Functions2464Rick_Bailey

First post 09/20/18,Last post 09/20/18Is 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?Formulas and Functions241paulayh21

First post 09/20/18,Last post 02/20/19Good morning Smart Sheet community. I have been tasked to create a commission tracking smart sheet for our sales team. If you have a prefab sheet(s), and willing to share, I would be forever grateful. Otherwise, here was my thoughts on…Formulas and Functions735Mtmoroni

First post 09/20/18,Last post 09/20/18Hi, We have a sheet called project milestones for managing the steps of LIVE manufacturing an installation projects. I'd like when our production manager selects a tick box on a specific row, that 4no columns within the row are aut…Formulas and Functions381Stuart.d

First post 09/20/18,Last post 09/20/18I have a nested IF formula to change the status ball colors as values are entered down the line. They usually get entered one at a time. If column A gets filled then it turns green, then if B gets filled it turns yellow, etc.. Column A…Formulas and Functions32SYSPK

First post 09/19/18,Last post 10/01/18I'm looking for a way to auto number my WBS. I believe I have found a way using logic functions combined with the Match function. The issue I am running into is that I am unable to reference a cell, say Task5 and then specify its "si…Formulas and Functions661Jacob Slaathaug

First post 09/19/18,Last post 12/31/19Is there a way to have a cell auto calculate age (years and months) from birth date in another cell?Formulas and Functions37511Nicole Graham

First post 09/19/18,Last post 01/30/20Hi, I'm trying to get a Percentage of Child Rows with Check Mark. Can anyone help out? =COUNTIF(CHILDREN(), 1) + "" returns the count of checked boxes. =(COUNT(CHILDREN(), 0) + "" returns the count of total rows, c…Formulas and Functions10521craigwaco

First post 09/18/18,Last post 09/23/18I'm using this vlookup formula to pull back a value from a data sheet. Let's say the value of NUMBER81 is 123456 and when I type in the formula on the desire sheet it returns "No match". When I go to the ACCOUNTS sheet and …Formulas and Functions382Mike L.