Formulas and Functions

Formulas and Functions

Archives for November 2018

Discussion List

  • admin58441
    admin58441 107 views 5 comments Most recent by Andrée Starå Formulas and Functions
    First post 11/21/18,Last post 11/23/18
    Hi All, I am trying to build a formula with IF, AND conditions but i am unsuccessful, can any one help me to sort out this please.  Here is the forumula =IF(AND([Position at Practice]227 = "Receptionist", [Current MECA Step]22…
    Formulas and Functions1075
    admin58441
  • Michele Deo
    First post 11/21/18,Last post 11/22/18
    I'm trying to have a conditional format occur in a status column, when the % Complete column hits a certain condition, for example: 1.  If % Complete = 100%, place "Complete" in the Status Column, and conditional format will cha…
    Formulas and Functions511
    Michele Deo
  • lisarae723
    lisarae723 14 views 3 comments Most recent by Andrée Starå Formulas and Functions
    First post 11/21/18,Last post 11/21/18
    Hi everyone,   While at Engage18, I heard there would be a way to have a row drop to the bottom of the sheet, once completed. Does anyone know if it's at all possible right now, or is this something they are still working on? 
    Formulas and Functions143
    lisarae723
  • vwhisel
    vwhisel 20 views 1 comment Most recent by Paul Newcome Formulas and Functions
    First post 11/21/18,Last post 11/21/18
    Hello,  Issue: I need to create a date column that will produce a forecasted date, based on a manually entered date plus X amount of days, based on a certain value (phase). Example:  If Phase is "SOW Draft/Negotiations", add 60…
    Formulas and Functions201
    vwhisel
  • PamelaH
    PamelaH 154 views 4 comments Most recent by PamelaH Formulas and Functions
    First post 11/21/18,Last post 11/26/18
    Hi, which formula can I use to calculate the expected Progress (depending on the current date)? Thanks
    Formulas and Functions1544
    PamelaH
  • dave.mcpherson56751
    First post 11/21/18,Last post 11/21/18
    Hi, i am hoping some can help or confirm if this is possible. The background: We are capturing data from employees working at 400+ locations. this is being done by a form. the data captured on the form is then being pushed out to individu…
    Formulas and Functions32
    dave.mcpherson56751
  • UnaB
    UnaB 692 views 11 comments Most recent by Paul Newcome Formulas and Functions
    First post 11/20/18,Last post 03/23/20
    I'm trying to see if formulas can reference reports or just root SS. I ran a report for all late tasks assigned to all people in a department. I then wanted to use a COUNTIF formula that would sum up all the late tasks of a certain person.…
    Formulas and Functions69211
    UnaB
  • Rofonic27
    Rofonic27 14 views 1 comment Most recent by eric.o Formulas and Functions
    First post 11/20/18,Last post 11/30/18
    I have ranges of data in multiple forms.  There is criteria that must match from each to be summed on a third sheet.  The criteria are GL Accounts and a month Code.  One of the month codes is listed as 12 Month.  The intent of that code is…
    Formulas and Functions141
    Rofonic27
  • CieraB
    CieraB 2 views 4 comments Most recent by Andrée Starå Formulas and Functions
    First post 11/20/18,Last post 11/30/18
    I am trying to enter a formula that will count when the name Kara is listed and the status is listed. I keep either getting an error or it will double count. Please help. The end goal is to find ou how many each person has in each status l…
    Formulas and Functions24
    CieraB
  • Jef Forward
    First post 11/20/18,Last post 11/21/18
    Hello - I need some help with the children formula.  I am trying to reference another sheet and only pull info from the children of specific hierarchies. The only way that I can do it, as of now and with my current skill set, is if I sele…
    Formulas and Functions292
    Jef Forward
  • CHealy
    First post 11/20/18,Last post 08/13/19
    I'm having difficulty with a few formulas wherein I'm attempting to SUM and COUNT only the CHILD rows so as to not duplicate amounts or counts (ie. need to exclude the Parent Row).  The formula below works when the BOLD text is not insert…
    Formulas and Functions18626
    CHealy
  • Buildcore
    Buildcore 99 views 1 comment Most recent by Andrée Starå Formulas and Functions
    First post 11/20/18,Last post 11/20/18
    When a deal in my pipedrive reaches a new stage 'Job Scheduled' it automatically adds a new row to Smart Sheets with a Task Name and Duration. What I need Smartsheets to do next is automatically enter a starting and end date based off of t…
    Formulas and Functions991
    Buildcore
  • Ryan- Impact
    Ryan- Impact 331 views 4 comments Most recent by Paul Newcome Formulas and Functions
    First post 11/20/18,Last post 11/21/18
    I have a google form that updates the prices of materials. On google sheets to display the most updated price of the material I have a Newest Entry Column =MAXIFS(Timestamp:range, Material cell) then in the material price columns I have an…
    Formulas and Functions3314
    Ryan- Impact
  • Lori Khoury
    Lori Khoury 1 view 1 comment Most recent by Sterling Formulas and Functions
    First post 11/19/18,Last post 11/19/18
    Can someone tell me what I am doing wrong in this formula? =COUNTIFS({HITRUST CAP Milestone Plan v2 - EIS & Sec Range 3}"remediated") AND({HITRUST CAP Milestone Plan v2 - EIS & Sec Range 4}<=(2018,6,30),>=(2018,4,1…
    Formulas and Functions11
    Lori Khoury
  • SYSPK
    SYSPK 1.1K views 27 comments Most recent by Paul Newcome Formulas and Functions
    First post 11/19/18,Last post 07/17/20
    I'd like to get a list of unique values in a range. I'm finding a lot on the community about how to flag duplicates, but not sure how to get a list where each unique value appears in a cell. Screenshot below is what I'm looking for. My goa…
    Formulas and Functions106927
    SYSPK
  • davidv
    davidv 95 views 6 comments Most recent by bcwilson.ca Formulas and Functions
    First post 11/19/18,Last post 11/26/18
    Our Smartsheet has been build with time entered that way 2h00 0h15 0h15 1h00 3h00 2h00 1h00 0h15 0h15 3h00 0h30 18h00   I want to transform that value automatically in minutes (120 / 15 / 15 / 60 / 180 / 120 etc)   Any idea…
    Formulas and Functions956
    davidv
  • KO_in_Colo
    First post 11/18/18,Last post 11/19/18
    I feel silly, but I'm stumped.  I'm trying to do a countif with a sheet reference. I want to count how many rows in my Lease Tracker 2018 sheet have Agricultural in the Category column, but I keep getting #UNPARSEABLE.  What am I doing wro…
    Formulas and Functions122
    KO_in_Colo
  • dave.mcpherson56751
    First post 11/18/18,Last post 11/19/18
    Hi, I am trying to count the amount of entries of another sheet that match the name of a row on my master sheet. I have tried to do something with count Ifs and [email protected] but have got a bit lost. So on my master sheet i have the name Bas…
    Formulas and Functions1346
    dave.mcpherson56751
  • dave.mcpherson56751
    First post 11/18/18,Last post 11/19/18
    Hi, is it possible to capture the amount of entries that have come in from a form? the form is constantly being populated so i am looking for a live number if possible? Any help appreciated. thank you Dave
    Formulas and Functions1169
    dave.mcpherson56751
  • Jessb9187
    Jessb9187 4 views 3 comments Most recent by Sterling Formulas and Functions
    First post 11/16/18,Last post 11/19/18
    I'm trying to create a condition based upon user submitted data for over budget flight requests. If the flight type is a One-Way, then whatever the pre-tax amount should be subtracted by the budget amount to give me the over budget amount.…
    Formulas and Functions43
    Jessb9187
  • kjthacker
    kjthacker 455 views 2 comments Most recent by kjthacker Formulas and Functions
    First post 11/16/18,Last post 11/16/18
    Hi, I would like to build a pie chart that reflects some number of red, yellow and/or green status in a bug tracking sheet. Since pie charts seem to require numeric data, I think that I need to craft a formula that will assign value of 1 …
    Formulas and Functions4552
    kjthacker
  • Jess Merritt
    Jess Merritt 330 views 10 comments Most recent by Andrée Starå Formulas and Functions
    First post 11/16/18,Last post 09/26/19
    We are wondering if anyone knows how to build a public facing sheet that has a search box where customers could type in a word or two and then have it return any hits. The data that would be searched could be linked to another sheet and th…
    Formulas and Functions33010
    Jess Merritt
  • paulc
    First post 11/16/18,Last post 11/16/18
    My duration roll ups for child/parents are not working as expected. Can anyone advise of things I could try to resolve why only one parent has a duration total? (Image)
    Formulas and Functions163
    paulc
  • Mary Miller
    Mary Miller 22 views 5 comments Most recent by Mike Wilday Formulas and Functions
    First post 11/15/18,Last post 11/16/18
    I am working on a sheet, and I am very new to this application, where I am tracking P.O.'s, date ordered, date received, etc... and I have 2 columns (at the far right of my sheet or end) that I would like to  If the P.O. Total is < or …
    Formulas and Functions225
    Mary Miller
  • rmpedro68986
    First post 11/15/18,Last post 11/15/18
    I've created a column titled "Outstanding Approvals", I would like the column to auto populate the check box when "Approval Status" is empty AND when "Start Date" has already begun. I'm using it so I can pull …
    Formulas and Functions183
    rmpedro68986
  • Jessb9187
    Jessb9187 50 views 2 comments Most recent by Mike Wilday Formulas and Functions
    First post 11/15/18,Last post 11/15/18
    Hey everyone! I just started using Smartsheet and so far am blown away by everything! At the moment, I'm trying to use the SUMIF formula to calculate a total cost but only if the number being used is below 0. For example, If Column A is a…
    Formulas and Functions502
    Jessb9187
  • Kelly Ospina
    Kelly Ospina 11 views 8 comments Most recent by Andrée Starå Formulas and Functions
    First post 11/15/18,Last post 11/15/18
    Good morning, we have a sheet we are using to track leads. I've been asked to set up an automated update request to send leads that have a certain status out on the first business day of each month.  In the automated actions, there is no …
    Formulas and Functions118
    Kelly Ospina
  • Brian W
    Brian W 69 views 5 comments Most recent by Isaac Jose Formulas and Functions
    First post 11/14/18,Last post 07/25/19
    Is there a way to use the MONTH function with COLLECT to, say, find the highest date in a column that is in the month of October? Basically, to simplify a formula like this: =MAX(COLLECT([Due Date]:[Due Date], [Due Date]:[Due Date], >=…
    Formulas and Functions695
    Brian W
  • Rickard
    Rickard 3 views 2 comments Most recent by Paul Newcome Formulas and Functions
    First post 11/14/18,Last post 11/15/18
    Hi! I have a tricky formula I'm trying to calculate, but with no success so far...    I have to different sheets that I'm looking on.  Sheet1: Planning and monitoring sheet Sheet2: Work reports The formula challenge: In Sheet2 our…
    Formulas and Functions32
    Rickard
  • AmyTed
    AmyTed 89 views 1 comment Most recent by Stefan 7k Formulas and Functions
    First post 11/13/18,Last post 11/14/18
    Hi,  I'm trying to summarize hours worked on each project for each month. My list of projects changes regularly with some coming in and some going out. Data is collected weekly so there will be multiple entries for each project. My though…
    Formulas and Functions891
    AmyTed