Formulas and Functions

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

Formulas and Functions

Discussion List

  • Jennifer Workman
    Accepted Answer Jennifer Workman 30 views 9 comments Most recent by Andrée Starå
    First post 01/17/20,Last post 01/18/20
    Hello, I am trying to do a VLOOKUP from another sheet based on a value in my current/new sheet. I want to MATCH the Task Name column first, THEN go to column 41(old sheet) and enter the date from that column onto my new sheet. This is wh…
    Formulas and Functions309
    Jennifer Workman
  • Chichi
    Chichi 22 views 7 comments Most recent by Paul Newcome
    First post 01/17/20,Last post 01/17/20
    I need help basically calculating lead time for a portion of a project. For example to Calculate Planned days I need a formula that would subtract the date the Samples where approved from the date the Mass Production is scheduled for deliv…
    Formulas and Functions227
    Chichi
  • Kristie Diersen
    SummaryAccepted Answer
    Accepted Answer Kristie Diersen 9 views 1 comment Most recent by Paul Newcome
    First post 01/17/20,Last post 01/17/20
    I am trying to find a way to do a monthly summary of the number items each month in Published, and Expired Alert Status. https://us.v-cdn.net/6031209/uploads/277/JKXO5DMT4B8I.jpg Does anyone know what would be the best way to complete th…
    Formulas and Functions91
    Kristie Diersen
  • Shawn Borror
    Answered Shawn Borror 25 views 11 comments Most recent by Paul Newcome
    First post 01/15/20,Last post 01/17/20
    I have a sheet of active projects. https://us.v-cdn.net/6031209/uploads/900/8XJWC47H1XNC.png I am trying to create a report filter on the Status column. However Status does not show up as a column either in row report or summary report. …
    Formulas and Functions2511
    Shawn Borror
  • Paul Newcome
    Paul Newcome 65 views 8 comments Most recent by Paul Newcome
    First post 11/21/19,Last post 01/17/20
    While trying to figure something else out, I stumbled across a new option for having specific actions date/time stamped using the stem generated Modified (date) type column and the new Copy Row automation. . DISCLAIMER: This does not sc…
    Formulas and Functions658
    Paul Newcome
  • Grace_J_Dravenack32331
    Answered Grace_J_Dravenack32331 9 views 2 comments Most recent by Andrée Starå
    First post 01/17/20,Last post 01/17/20
    I am using a content calendar template. All the formulas were already a part of the standard content calendar Smartsheet. We noticed that since the new year (after January 2020), all the dates automatically shifted. The December 2019 paren…
    Formulas and Functions92
    Grace_J_Dravenack32331
  • Ray B
    Answered Ray B 22 views 6 comments Most recent by Ray B
    First post 01/16/20,Last post 01/17/20
    Good afternoon Smartsheet Community: I am trying to add to this formula: =("Priorities for 9/23 Week: " + COUNT(CHILDREN())) I want it to Count the number of Done: Priorities for 9/23 Week 1 out Of 10. Thanks
    Formulas and Functions226
    Ray B
  • Lauren Ford
    Accepted Answer Lauren Ford 21 views 8 comments Most recent by Andrée Starå
    First post 01/14/20,Last post 01/17/20
    Hello! I'm trying to send an update request to be alerted during the following time periods: 30 days after the initial row was added 60 days after the initial row was added 90 days after the initial row was added I have an "entry date…
    Formulas and Functions218
    Lauren Ford
  • tgilles
    Parent vs child flagAccepted Answer
    Accepted Answer tgilles 18 views 2 comments Most recent by Paul Newcome
    First post 01/17/20,Last post 01/17/20
    Hello, I'm wondering whether it is possible or not to write a formula to indicate whether a certain cell is a parent or a child. The sheet I'm working with only has 2 levels, and I'd like to create a column that is able to delineate the t…
    Formulas and Functions182
    tgilles
  • Mark Strecker
    Mark Strecker 43 views 15 comments Most recent by Andrée Starå
    First post 01/09/20,Last post 01/17/20
    Hello, I have two fields in a given row, "Plates Needed" and "Reason for Reorder", that I want the contents of deleted when someone selects the "Remake Complete" checkbox. Is there a way to do this using a for…
    Formulas and Functions4315
    Mark Strecker
  • rbuckner
    Answered rbuckner 13 views 2 comments Most recent by Paul Newcome
    First post 01/17/20,Last post 01/17/20
    I am trying to get a status to report as flagged in certain situations and not others. I need it to be specific, because I have alerts for when the status is flagged and I don't want it to happen when it isn't necessary. Right now I have…
    Formulas and Functions132
    rbuckner
  • BryanHeft
    BryanHeft 147 views 26 comments Most recent by Andrée Starå
    First post 10/29/18,Last post 01/17/20
    I have a column set as Contact List and the Allow Multiple Contacts per Cell field is checked.  I am using COUNTIF statements to count how many people are assigned to the tasks for a dashboard.  I am using the formula: =COUNTIF({Project L…
    Formulas and Functions14726
    BryanHeft
  • Leann Gibson
    Answered Leann Gibson 20 views 1 comment Most recent by Andrée Starå
    First post 01/16/20,Last post 01/17/20
    Does anyone know why I am getting this message "Cannot add this user to this group"? I created the group, however it is not letting me add everyone I need to.
    Formulas and Functions201
    Leann Gibson
  • Sandy Glassberg
    Answered Sandy Glassberg 33 views 3 comments Most recent by Paul Newcome
    First post 01/16/20,Last post 01/17/20
    Context: Please see screenshot attached. Project Manager "Fred" has 2 "active" projects in January: Project 1 and Project 2, which are Children under "Fred". This count of 2 is determined by some percentage of…
    Formulas and Functions333
    Sandy Glassberg
  • Terry Birse
    Answered Terry Birse 10 views 2 comments Most recent by Paul Newcome
    First post 01/17/20,Last post 01/17/20
    I have created a template suite and my project plan has links to other docs within the suite. When "Create as New",the embedded links only link back to the original template suite and not the new files. Is therre a way to make th…
    Formulas and Functions102
    Terry Birse
  • Jenny McFarland
    Sort functionAnswered
    Answered Jenny McFarland 11 views 1 comment Most recent by Andrée Starå
    First post 01/17/20,Last post 01/17/20
    Hi, Can anyone help me please - how do you disable/enable sort function in sheets? We have some where you can sort with editor access, and some which you cannot sort with the same access. Many Thanks
    Formulas and Functions111
    Jenny McFarland
  • Shanon Stoklosa
    Answered Shanon Stoklosa 33 views 1 comment Most recent by Eric M Oliveira
    First post 01/16/20,Last post 01/17/20
    https://us.v-cdn.net/6031209/uploads/547/YN8ETXTSH4TX.png https://us.v-cdn.net/6031209/uploads/880/IG7ANCXHG0K5.png I am using smartsheets to access the availability of team members to appropriately assign tasks. How this works is that our…
    Formulas and Functions331
    Shanon Stoklosa
  • Pamb10
    Accepted Answer Pamb10 14 views 2 comments Most recent by Pamb10
    First post 01/16/20,Last post 01/16/20
    I have a need to count rows where: Issue Type = Story Labels include LND-Product Status = Done Year of Resolution Date = 2020 COUNTIFS({JIRA Tickets Issue Type}, "Story", {JIRA Tickets Labels}, "LND-Product", {JIRA Tic…
    Formulas and Functions142
    Pamb10
  • MCorbin
    Accepted Answer MCorbin 23 views 2 comments Most recent by MCorbin
    First post 01/16/20,Last post 01/16/20
    I have a sheet that captures phone queue data by operator and date. Date Operator # Calls Weekday (formula) 1/1 Bob 10 4 1/1 Sue 15 4 1/1 Ralph 10 …
    Formulas and Functions232
    MCorbin
  • Allan Segal
    If Then function neededAccepted Answer
    Accepted Answer Allan Segal 11 views 2 comments Most recent by Allan Segal
    First post 01/16/20,Last post 01/16/20
    I have a column that is "Days to Complete" that is calculated by =ABS((NETWORKDAY([Due Date]1, [Todays Date]1))) But I have another column that is called "Status" which is a drop down list . If I set this column to &q…
    Formulas and Functions112
    Allan Segal
  • Maricarmen
    Accepted Answer Maricarmen 38 views 17 comments Most recent by Paul Newcome
    First post 01/14/20,Last post 01/16/20
    Hello! Stuck testing out formulas to add a Column [Children Due Soon] that checkmarks if a parent row [Task Name] has any children task is due [Due] in the next 7 days. I've been trying to edit the famous RGB formula for this, but still no…
    Formulas and Functions3817
    Maricarmen
  • Daryl Lee
    Accepted Answer Daryl Lee 41 views 7 comments Most recent by Paul Newcome
    First post 01/07/20,Last post 01/16/20
    Hello! I am trying to count to see how often a specific word, in this case a person's name, appears in a range of text. I've looked over similar questions and have started a formula, but I end up with an #INVALID OPERATION error. My for…
    Formulas and Functions417
    Daryl Lee
  • Sinema
    Answered Sinema 29 views 3 comments Most recent by Sinema
    First post 01/15/20,Last post 01/16/20
    Greetings, I'm probably over thinking this but I'm trying to do the following; I have a customer account number column and an area column. I would like to count the total number of unique customer account numbers in [Customer Accounts] tha…
    Formulas and Functions293
    Sinema
  • System
    Answered System 28 views 3 comments Most recent by Paul Newcome
    First post 01/15/20,Last post 01/16/20
    This discussion was created from comments split from: Calculate Future Date.
    Formulas and Functions283
    System
  • Kitty
    Answered Kitty 12 views 2 comments Most recent by Andrée Starå
    First post 01/16/20,Last post 01/16/20
    I have instructors going on the field to teach. I would like to generate an automatic email with the link to our Smartsheet form for an Instructor Field Report to be filled out. Is this possible? Thank you! Kitty Sandel
    Formulas and Functions122
    Kitty
  • Pam Grant
    Answered Pam Grant 12 views 1 comment Most recent by Andrée Starå
    First post 01/16/20,Last post 01/16/20
    We are asking for updates to quality documents. The form asks for the document number and the title. Is there way to auto-populate the title in the Form once the number is entered?
    Formulas and Functions121
    Pam Grant
  • Brent Allen
    Accepted Answer Brent Allen 16 views 2 comments Most recent by Brent Allen
    First post 01/15/20,Last post 01/16/20
    I"m trying to create a formula, referencing another sheet, with qualifier of date range, and 'Status' being 'Live' OR 'Ended'. I have this: =COUNTIFS({Test Start Date}, <=DATE(2020, 1, 31), {Test Start Date}, >=DATE(2020, 1, 1)…
    Formulas and Functions162
    Brent Allen
  • Frank Falco
    Accepted Answer Frank Falco 41 views 9 comments Most recent by Paul Newcome
    First post 01/14/20,Last post 01/16/20
    I have the following three columns Idx, Stage and Steps. Steps is a Dropdown Multi-Select column. I want to use the step value from column in another sheet to find which row that step belongs to so I can return the Idx or Stage values from…
    Formulas and Functions419
    Frank Falco
  • H. Obringer
    Accepted Answer H. Obringer 35 views 9 comments Most recent by Paul Newcome
    First post 01/14/20,Last post 01/16/20
    So I have been looking through the community for a while and have seen a couple solutions mildly similar to what I am looking to do, but can't quite get it right. Feel free to link me to another solution if you feel it would solve this pro…
    Formulas and Functions359
    H. Obringer
  • Adina Foley
    Accepted Answer Adina Foley 35 views 9 comments Most recent by Paul Newcome
    First post 01/14/20,Last post 01/16/20
    Hi there, I am currently trying to merge two sheets that have been created in my company. One has a list of projects/sales on it the other one has just the hardware for each project/sale. I had tried adding a new child row for each product…
    Formulas and Functions359
    Adina Foley