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

  • Jessica Langston
    Auto Fill Fields in SheetAnswered Question
    Answered Jessica Langston 5 views 2 comments Most recent by Andrée Starå
    First post 02/25/20,Last post 02/25/20
    Hello, I have 2 columns I am looking to auto populate based on what is selected in another column. Depending on the requestors name the Manager and Director will populate. The thing I am having trouble with is that multiple requestors ar…
    Formulas and Functions52
    Jessica Langston
  • Tanya Boehmke
    Accepted Answer Tanya Boehmke 71 views 41 comments Most recent by Tanya Boehmke
    First post 01/24/20,Last post 02/25/20
    Hello, I've determined the issue with this formula is that the cell it is pulling from is formatted as text, not a number. I know I need to use the VALUE function to correct this, but I've tried this many different ways and cannot get it t…
    Formulas and Functions7141
    Tanya Boehmke
  • Nathan Nguyen
    Accepted Answer Nathan Nguyen 11 views 4 comments Most recent by Nathan Nguyen
    First post 02/25/20,Last post 02/25/20
    Hello Smartsheet community, I have been trying to resolve my error by looking online and this forum, but still can't fix my error. I'm hoping you smart experts can help me. I have 2 columns: Completion Status and Status. The Completion Sta…
    Formulas and Functions114
    Nathan Nguyen
  • Apolonio Fama
    Average FormulasAnswered Question
    Answered Apolonio Fama 12 views 3 comments Most recent by Paul Newcome
    First post 02/25/20,Last post 02/25/20
    Hi all, Need help with trying to get a formula for Averaging Transit Days for certain criterias in Carrier and Origin. Ultimately I'm trying to build a dashboard of transit days for each origin location. Below if formula I am trying, but…
    Formulas and Functions123
    Apolonio Fama
  • Peggy P
    Answered Peggy P 15 views 1 comment Most recent by Paul Newcome
    First post 02/24/20,Last post 02/25/20
    I'm trying to get a formula to work in the Sheet Summary for a sheet I have that collects data on updates I would like this formula to tell me the Average duration of a specific type of update for a specific year. The columns my formula …
    Formulas and Functions151
    Peggy P
  • Lisa Burgess
    Answered Lisa Burgess 8 views 1 comment Most recent by Paul Newcome
    First post 02/25/20,Last post 02/25/20
    Below are my cells and formula which keeps returning INVALID: https://us.v-cdn.net/6031209/uploads/005/P93KDRXB6I0I.png
    Formulas and Functions81
    Lisa Burgess
  • Lisa Burgess
    Answered Lisa Burgess 12 views 1 comment Most recent by Paul Newcome
    First post 02/25/20,Last post 02/25/20
    Below is my formula, but I am not getting it right: =SUMIFS({2020 OE Revenue Actual & Projected Range 1}, {2020 OE Revenue Actual & Projected Range 2}, "New Business", [({2020 OE Revenue Actual & Projected Range 3}, …
    Formulas and Functions121
    Lisa Burgess
  • Lubica Zummerova
    Answered Lubica Zummerova 10 views 1 comment Most recent by Paul Newcome
    First post 02/25/20,Last post 02/25/20
    Hi all, I was wondering if there is a formula that can take my start date that occurs at any random day of the week in column A (lets say tasks starts on a Tuesday 25/02/2020) and give me a date of the start of the week in column B (Mon…
    Formulas and Functions101
    Lubica Zummerova
  • Allison H
    Answered Allison H 7 views 3 comments Most recent by Paul Newcome
    First post 02/25/20,Last post 02/25/20
    I'm trying to get a count of open RAID items. We have the following statuses in the status dropdown: New, In Progress, Backlog, Requires Validation, and Complete. What is wrong with this formula?: =COUNTIFS([Type (RAAIDD)]:[Type (RAAIDD)…
    Formulas and Functions73
    Allison H
  • Ray B
    Accepted Answer Ray B 9 views 5 comments Most recent by Paul Newcome
    First post 02/25/20,Last post 02/25/20
    Smartsheet Community: I need a little help with this Formula: =IF([email protected] = 1, "Green", IF([email protected] <> 1 "Grey", "Red") I would like to have the row turn Grey if None of the Children Checkbox are Chec…
    Formulas and Functions95
    Ray B
  • Shirley Bittner
    Accepted Answer Shirley Bittner 9 views 2 comments Most recent by Paul Newcome
    First post 02/25/20,Last post 02/25/20
    When I use an automation workflow from a form question hidden as I have the formula as the default value of that question, how can I get it to work it's formula instead of it overriding as a text? So when I put in the formula in the defaul…
    Formulas and Functions92
    Shirley Bittner
  • dlnvtl
    dlnvtl 409 views 10 comments Most recent by Paul Newcome
    First post 08/20/19,Last post 02/25/20
    I'm new to smartsheet and trialing it at the moment. I have a question, I hope you can help me with: I have a portfolio of companies in a main sheet and their debt issues, and related stuff in several sheets. Rows are companies/debt issue…
    Formulas and Functions40910
    dlnvtl
  • Xander Brennan
    Accepted Answer Xander Brennan 22 views 11 comments Most recent by Andrée Starå
    First post 02/19/20,Last post 02/25/20
    I have a sheet of contracts with 30 columns (status/progression/etc). The columns are updated by different people in different departments. Is there anyway to create a form that would have a dropdown of the contract ID and then the cells I…
    Formulas and Functions2211
    Xander Brennan
  • Pam Grant
    Answered Pam Grant 32 views 3 comments Most recent by Andrée Starå
    First post 01/16/20,Last post 02/25/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 Functions323
    Pam Grant
  • Tanya Boehmke
    Accepted Answer Tanya Boehmke 43 views 11 comments Most recent by Andrée Starå
    First post 02/12/20,Last post 02/25/20
    Hello, I have two sheets. On one I have a column noting the status of a contract, in drop-down style. I would like for when the status is complete on this sheet, for it to check a box on another sheet. Is this possible?
    Formulas and Functions4311
    Tanya Boehmke
  • Readiness Project
    First post 02/25/20,Last post 02/25/20
    Here is my issue : TASK1 = [Justin], [Celine] | Duration=10days | %Allocation=20% | [*equivalent to 2 worked-days*] TASK2 = [Celine] | Duration=5days | %Allocation=20% | [*equivalent to 1 worked-day*] I am looking to build…
    Formulas and Functions70
    Readiness Project
  • Peter Kafui Doviavu
    Answered Peter Kafui Doviavu 19 views 6 comments Most recent by Andrée Starå
    First post 02/21/20,Last post 02/25/20
    I have a conditional formatting in a sheet which colors Red if the due date is passed. Now i need a simple formula to populate all the values in Red in a Metric. Any Help?
    Formulas and Functions196
    Peter Kafui Doviavu
  • Briana Caterino
    Answered Briana Caterino 26 views 3 comments Most recent by Andrée Starå
    First post 02/20/20,Last post 02/25/20
    Good Morning! I've created a conflicts of interest survey and I'm looking for a formula to then reflect on my dashboard for reporting. Can someone please help me identify how i can use a formula to count if someone from a particular depar…
    Formulas and Functions263
    Briana Caterino
  • Jennifer Mahoney
    Conditional Linking?Answered Question
    Answered Jennifer Mahoney 42 views 7 comments Most recent by Andrée Starå
    First post 02/11/20,Last post 02/25/20
    Hello, I was wondering if there was a way to unLink cells if a condition is met, i.e. if Column1 is checked then UnLink all cells in all rows this would be to lock values coming in once checked or if that is NOT possible ... If …
    Formulas and Functions427
    Jennifer Mahoney
  • Hoi Man Cheung
    Average Formula IssueAnswered Question
    Answered Hoi Man Cheung 21 views 8 comments Most recent by Andrée Starå
    First post 02/15/20,Last post 02/25/20
    Hi there, i am trying to average cells that were built up by formulas. So basically the Column 1-7 are the scores when the task is done , it will turns into 100 when the task is complete, and where the right handside column would need to …
    Formulas and Functions218
    Hoi Man Cheung
  • Stelina Todorova
    Answered Stelina Todorova 38 views 11 comments Most recent by Andrée Starå
    First post 02/20/20,Last post 02/25/20
    I have set up an automation rule to, when triggered, copy rows to another sheet. My trigger is a certain field that changes from 0 to 1. I've tried a couple of times and non of my rows are copied. To test it, I added to the same automatio…
    Formulas and Functions3811
    Stelina Todorova
  • Lauren Goedereis
    Answered Lauren Goedereis 19 views 2 comments Most recent by Andrée Starå
    First post 02/21/20,Last post 02/25/20
    Hello, Wanting to be able to enter 1/1/20 & auto fill the rest of the month? Option: I would like to total each week which would need to skip 1 row. If not an option that's fine.
    Formulas and Functions192
    Lauren Goedereis
  • David Budnick
    David Budnick 63 views 5 comments Most recent by Andrée Starå
    First post 02/22/19,Last post 02/25/20
    Good day all, I've looked around in the community about locking or freezing a current date, but I'm still having issues. My goal is to lock a start date that's triggered by entering a X and have that current date freeze until the x is remo…
    Formulas and Functions635
    David Budnick
  • Jon Stikka
    Answered Jon Stikka 12 views 1 comment Most recent by Andrée Starå
    First post 02/24/20,Last post 02/25/20
    We have a large team all working within a workspace, and they already get a TON of email. Is there a way to set up a single daily notification for new items assigned to team members, without it being an email for each sheet? We have abou…
    Formulas and Functions121
    Jon Stikka
  • Noelle Nordstrom
    Noelle Nordstrom 33 views 8 comments Most recent by Paul Newcome
    First post 02/20/20,Last post 02/25/20
    New to SmartSheet and I promise I have been looking around for an answer, but without any luck so I thought I would throw it out to the community... I am looking to sum up all entries in column in a summary total at the top of my sheet, b…
    Formulas and Functions338
    Noelle Nordstrom
  • Jeffrey Calderon
    Answered Jeffrey Calderon 17 views 3 comments Most recent by Paul Newcome
    First post 02/22/20,Last post 02/25/20
    https://us.v-cdn.net/6031209/uploads/010/S3NB15U77LCD.png Hello, I am trying to find the index number of a matching number in column7. So if Column7 was 56 I would expect that this function =MATCH([Column7]1, (COLLECT([Column7]:[Column7]…
    Formulas and Functions173
    Jeffrey Calderon
  • Megha Natani
    Answered Megha Natani 10 views 1 comment Most recent by Stephan Hertig
    First post 02/25/20,Last post 02/25/20
    I need to count the total no. of check marked cells in a column
    Formulas and Functions101
    Megha Natani
  • Betsy Herczeg-Konecny
    Answered Betsy Herczeg-Konecny 11 views 1 comment Most recent by raltamirano
    First post 02/24/20,Last post 02/24/20
    Hi, I'd like to set up conditional formatting to let me know if the "Live Date" column on my grid is indeed 4 weeks from the "Submitted Date" (Submitted is an auto-populated cell if that matters.) I know I need a helper…
    Formulas and Functions111
    Betsy Herczeg-Konecny
  • Mary_A
    Accepted Answer Mary_A 19 views 2 comments Most recent by Mary_A
    First post 02/22/20,Last post 02/24/20
    It could be I misremembering --- I have a report that lists all the users tasks and the tasks are accompanied by a Done checkbox. Is there a way to allow users to check that box _from the widget on the dashboard_ rather than having to open…
    Formulas and Functions192
    Mary_A
  • LLaCosta
    Accepted Answer LLaCosta 67 views 13 comments Most recent by Paul Newcome
    First post 12/16/19,Last post 02/24/20
    Below is the current formula in a RYGB column. In the current set up, if the status is "not started" but the End date hasn't past, it will be yellow. Which is fine, however, I would like to update the formula to ALSO take into c…
    Formulas and Functions6713
    LLaCosta