Formulas and Functions
Archives for March 2020
Discussion List
-
Functionality QuestionAccepted AnswerAccepted Answer Marc Raskinski 17 views 6 comments 0 points Most recent by Debbie Sawyer Formulas and FunctionsFirst post 03/31/20,Last post 03/31/20Hello, I am more of an Excel guy who is attempting to wrap my head around Smartsheet. I have a scenario and I am thinking it can be done, it seems like I almost find the answer but just not quite. I have records which require attention…Formulas and Functions176Marc Raskinski -
First post 03/31/20,Last post 03/31/20I am bringing in 2 separate sheets into a sheet summary report using a custom column called "Total Revenue" which is a sum of each individual sheet's revenue rows The sheet summary report is showing correctly both rows fo…Formulas and Functions185Tony Conaghan
-
COUNTIFS in a multi-select contact columnAccepted AnswerAccepted Answer Wendy Young 23 views 6 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 03/31/20,Last post 04/01/20I am trying to count the number of times a name appears in a multi-select contact column, however, none of the proposed formulas are working. Please advise. Thank you! WendyFormulas and Functions236Wendy Young
-
How to find a word and return a valueAccepted AnswerAccepted Answer Marla Berge 21 views 7 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 03/31/20,Last post 04/01/20=IF([Trip Purpose]@row=(FIND"Morning"),"2-Day",IF[Trip Purpose]=(FIND"Afternoon"),"3-Swing",IF[Trip Purpose]=(FIND"Night"),"1-Grave", "Float") I'm trying to find the wo…Formulas and Functions217Marla Berge
-
Missing Source Data on DashboardAnswered QuestionAnswered Leah Granderson 13 views 1 comment 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 03/31/20,Last post 03/31/20I was just in my Dashboard and saw all of my charts and graphs. Now, when I log back-in, I am getting a Missing Source Data on my Dashboard. How can I recover?Formulas and Functions131Leah Granderson
-
Formula from One Column to autopopulate other column dropdownAnswered QuestionAnswered Chris Moore 16 views 1 comment 0 points Most recent by Debbie Sawyer Formulas and FunctionsFirst post 03/31/20,Last post 03/31/20Hi Need help with a formula. When "Yes" is selected manually in the Incidents Safety or Medical column (this has a manual drop down of Yes or No, I would like it to If "Yes" selected the "GS&S Incident Case&quo…Formulas and Functions161Chris Moore
-
Automation unsubscribed the creator automaticallyAnswered QuestionAnswered Gavriel Zarka 10 views 3 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 03/31/20,Last post 03/31/20Hi Guys. I encountered with something very strange in SS automation process I created, and I couldn't find a response to what I have been experiencing.I created a simple automation workflow that does the following: once the value in one of…Formulas and Functions103Gavriel Zarka
-
SUMIF with OR functionAccepted AnswerAccepted Answer Jessintha Nathan 18 views 7 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 03/31/20,Last post 04/01/20Hello! =COUNTIFS({Weekly Shop Visit Update Range 10}, >=[Start Date]17, {Weekly Shop Visit Update Range 10}, <=[End Date]17, {Weekly Shop Visit Update Range 7}, "M56-B", {Weekly Shop Visit Update Range 8}, [Column30]$2, {We…Formulas and Functions187Jessintha Nathan
-
If Status = "DNR Request - Approved" or "MRS Request Approved" Then formula question...Answered QuestionAnswered Jed Rusyniak 8 views 2 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 03/30/20,Last post 03/31/20In an effort to track the amount of time from submission to completion I'd like to have a formula help populate the [Actual Completion Date] by using the following logical expression...however I'm not getting this to work. If Status = &q…Formulas and Functions82Jed Rusyniak -
Sequential Unique identifier for two types of records in a sheetAnswered QuestionFirst post 03/30/20,Last post 04/03/20Hello Smartsheet community. I am in need of a solution that allows me to leverage an intake form to track Objectives and Key Results. I need to be able to sequentially generate unique ID's for each Objective and for each KR and I need thi…Formulas and Functions144bburrets
-
First post 03/30/20,Last post 03/31/20I'm exploring changing several of my more complex Smartsheets to rely less on links and more on data tables that are accessed by other sheets using VLOOKUP functions. Everything was fine until I needed to add a new column on one of the dat…Formulas and Functions71Jim Hook -
Formula for # of Room Nights omitting false dataAccepted AnswerAccepted Answer Darla Brown 18 views 12 comments 0 points Most recent by Darla Brown Formulas and FunctionsFirst post 03/30/20,Last post 03/31/20Hi, I have this formula in excel that I am trying to recreate in SmartSheet: =IF(OR(H7815=$D$1,I7815=$D$1),"",DATEDIF(H7815,I7815,"D")) Criteria is Check-In Date and Check-Out Date. The $D$1 is 12/31/9999 which I want t…Formulas and Functions1812Darla Brown
-
Formula assistanceAnswered QuestionFirst post 03/30/20,Last post 04/01/20Hi, Can anyone please help me setting up a formula that add 12 weeks to a date. Say if in my main Smartsheet I have populated 10 different dates then I want my "stats" sheet to have a count if formula that adds 12 weeks to all t…Formulas and Functions163sahilhq
-
Combining AND & OR in a formulaAccepted AnswerAccepted Answer Emma Pettit 17 views 3 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 03/30/20,Last post 03/31/20I have a sheet with the following three columns: --Was the question asked --Answer 1 y/n --Answer 2 y/n I want the formula to turn the flag red (cell already formatted for flags) if the question was asked, and either answer 1 or answer 2 w…Formulas and Functions173Emma Pettit
-
Can't update permissions on a WorkSpace.Accepted AnswerAccepted Answer Christina Reid 7 views 1 comment 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 03/30/20,Last post 03/30/20I've created three different WorkSpaces and have a core group I shared them with. In each Workspace is a set of sheets we use as a template for our new projects. I need for that core group to be able to right click on the set of sheets a…Formulas and Functions71Christina Reid
-
Using Join with LogicAnswered QuestionAnswered Donni Matthew 8 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 03/30/20,Last post 03/30/20Is there a way in Smartsheet to combine the data in the rows below AND include the Column Headers AND exclude the "No Impact" Status? https://us.v-cdn.net/6031209/uploads/383/3MV9PPWUTC5Z.jpg The end result should look something …Formulas and Functions83Donni Matthew
-
Index/Match? Not sure best function for my needsAnswered QuestionFirst post 03/30/20,Last post 03/30/20Hello, I have a very extensive sales tracker that I use to log charges and calculate the sales rep's commissions based on those charges. The commission percentages change depending on a variety of scenarios, so I created four columns with …Formulas and Functions152Annie Dunn
-
COUNTIF HELP W/BLANK CELLSAccepted AnswerAccepted Answer Donni Matthew 13 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 03/30/20,Last post 03/30/20I am trying to count the number of employees that have access to work remotely and have not been furloughed or laid off. The columns I'm looking at are: MODIFIED STATUS & TELECOMMUTER. The values in Modified Status are: Furlough, S…Formulas and Functions133Donni Matthew
-
Working to pull data in from four sheets to a master rollup sheet and keep getting an errorAnswered QuestionAnswered Heather Turley 11 views 1 comment 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 03/30/20,Last post 03/30/20#incorrect argument set =COUNTIFS({XXXX Assigned to}, Primary7, {Status}, <>"Complete") + COUNTIFS({XXX Liquid Assigned to}, Primary7, {Status}, <>"Complete") + COUNTIFS({XXXXX Assigned to}, Primary7, {Statu…Formulas and Functions111Heather Turley
-
First post 03/30/20,Last post 03/30/20https://us.v-cdn.net/6031209/uploads/740/H51E7NMRA811.png Help! Trying to display on dashboard specific date from two columns. The Internal / External Column has two drop down options namely Internal and External The Tier Column has six d…Formulas and Functions125Chris Moore
-
Formulas for visual symbolsAnswered QuestionFirst post 03/30/20,Last post 03/30/20Are there any formulas specific to visual symbols to simplify summary data for parent rows? I have a RYGB status for activities within a project and would like to be able to summarise the 'worst' status in the parent rows. For example, if…Formulas and Functions221Lexington
-
How to order rows from a Auto Number/System column?Accepted AnswerFirst post 03/30/20,Last post 04/01/20Hi I have a sheet A (SA) that is populated by copying row from sheet B (SB). When a new row is added to SB, I have a "Created" column set up as Auto Number/System column (System-generated column =Created (Date) ) which allows m…Formulas and Functions188xav
-
Percentage (%) complete formula - cancellation dateAnswered QuestionFirst post 03/30/20,Last post 03/30/20Hello, Due to COVID 19 we have had to cancel some courses up till the end of June. I need help to make a column with a formula to calculate the percentage of the course that was complete at time of course cancellation (any date between sta…Formulas and Functions373veenz20
-
Accepted Answer chris.italiano78351 18 views 2 comments 0 points Most recent by chris.italiano78351 Formulas and FunctionsFirst post 03/29/20,Last post 03/30/20Good Morning I have another formula question, i was hoping i can get some help. I would like to make the below RGB Symbol change according to the number below. E.g. I would like the below yellow Symbol to change when the -0.2 number change…Formulas and Functions182chris.italiano78351
-
Nested IF formula to return a symbol based on a rangeAnswered QuestionAnswered Laura Briggs 27 views 4 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 03/29/20,Last post 03/31/20Hi, I am trying to write a formula that will look at the value in a cell and return a RYG dependant on the value being either below the value in another cell, within a mid range or above the range. I think the issue I am having is in rela…Formulas and Functions274Laura Briggs
-
How do you sum totals of a column that include "No Match" cells?Answered QuestionFirst post 03/28/20,Last post 03/30/20Hello, I have been trying to search online but can't seem to find the answer to my question. What is the formula to sum cells in a column that include some "No Match" cells? Much appreciated, CheersFormulas and Functions211Matt May
-
Drop down selections not appearing in formAnswered QuestionAnswered Christina Riot 15 views 1 comment 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 03/28/20,Last post 03/30/20Is there a know issue with this or is there a max number of selections with forms? I have a contact list of 3000 names and it is not appearing as a drop down in my form. Other drop down's I have are indeed showing up and I am doing nothi…Formulas and Functions151Christina Riot
-
Showing Completion of tasks ON TIME: EARLY: LATEAnswered QuestionAnswered Brian Beatty 24 views 1 comment 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 03/28/20,Last post 03/30/20I work for an air cargo company. I seek help with calculating the above. Here is an overview of a typical nights work out on the ramp. A load planner and cargo loading team is tasked with getting airplanes loaded and sent out on time eve…Formulas and Functions241Brian Beatty
-
Logic and summaryAnswered QuestionAnswered dave.mcpherson56751 19 views 1 comment 0 points Most recent by Genevieve P Formulas and FunctionsFirst post 03/28/20,Last post 03/30/20Hi, We have 400 locations managed by approx 30 managers. I am trying to use logic to build a form that when manager 1 chooses their name they only see sites relating to them. To achieve this i have created columns with a drop down for eac…Formulas and Functions191dave.mcpherson56751
-
Need help with creating aging buckets.Accepted AnswerAccepted Answer Iris Fuentes 24 views 2 comments 0 points Most recent by Debbie Sawyer Formulas and FunctionsFirst post 03/28/20,Last post 03/31/20Hello, I have the following formula in excel but it did not transfer to Smartsheet. Is there a way to get this formula over? =LOOKUP(F2,{-100,7,14,21,30,45,90,180},{"0-7","7-14","14-21","21-30",&quo…Formulas and Functions242Iris Fuentes
