Formulas and Functions

Let the community help you with that tricky formula or function! See how others are utilizing the power of formulas and functions to save them time and create more powerful solutions with Smartsheet.

Discussion List

  • Dynamic numbering
    In Formulas and Functions
    Hi, I wanted to know if its possible to have the entry number as the Function + RAID item(R,A,I,D) + number. What I am looking for is for the R,A,I,D items to be named as per the functions- i.e. if its the 'first' R in Funciton1 , the resu…
    Unanswered
    13 0
    Question 13 views 0 comments Started by Naveen Srikakulam
  • Completed Time & Date based upon planned start and duration
    In Formulas and Functions
    Hello - i am trying to create a formula for planned completion date and time based upon start and durection, but am getting errors and not producing date values based upon another post I found with direction on how to do this. Here is a pi…
    Unanswered
    10 0
    Question 10 views 0 comments Started by Brenna Saunders
  • Formula to calculate End Date and Time based on Start Date, Time and Task duration
    In Formulas and Functions
    Hello, I'm looking for a formula to calculate the End Date and Time (MM/DD/YYYY)( HH:MM AM/PM) based on the Start Date and Start Time entered on a form using the Task standard routing time. (Image)
    Accepted
    341 29
    Accepted Answer 341 views 29 comments Most recent by Brenna Saunders
  • #INVALID COLUMN VALUE
    In Formulas and Functions
    How can I get rid of the error I'm getting using this formula? I realize that one column type is a checkbox and the other is a text/number. Is there a way to eliminate the "#INVALID COLUMN VALUE" that is displayed. The formula is…
    Answered - Pending Review
    14 3
    Answered - Pending Review 14 views 3 comments Most recent by ameyaa
  • Green, Yellow, Red Blue Formatting
    In Formulas and Functions
    Hello, Newbie here. Trying to set some formatting for some specific RYGB and I can only get it to work for Yellow. Any thoughts? I receive the #INCORRECT ARGUMENT error. Thanks in advance! =IF([Completed%]2 = 1, "Green", IF(A…
    Answered - Pending Review
    11 1
    Answered - Pending Review 11 views 1 comment Most recent by ameyaa
  • how to pick a specific item from a multi item dropdown cell
    In Formulas and Functions
    I have a (Dropdown, Multi select) column, cells with up to 10 items. I figured out how to count them (countm), but I cannot find a function, or figure out a way to pick inidividual items. EG, cell contains item L001 G02 L003 0L005, in no o…
    Answered - Pending Review
    9 2
    Answered - Pending Review 9 views 2 comments Most recent by David Joyeuse
  • Trying to find a match on 2 values between sheets then pull one or more values from the 2nd sheet
    In Formulas and Functions
    I have a master sheet with a two keys (eg, Customer and SKU). I am trying to find a match on these 2 values in a lookup table and pull in Price to the master sheet. I have tried a number of functions using VLOOKUP, INDEX, MATCH in combinat…
    Accepted
    12 3
    Accepted Answer 12 views 3 comments Most recent by David Joyeuse
  • Calculate number of days in a month
    In Formulas and Functions
    I'm trying to build a custom calendar in a sheet (Calendar app or view doesn't work in our use case), I want to calculate total number of days in a given month, but so far the day, date formulae need two set of dates. I know one way of sim…
    Accepted
    17 3
    Accepted Answer 17 views 3 comments Most recent by ameyaa
  • Changes since last week
    In Formulas and Functions
    Hello, Is there a way to show changes since last week to a 'date' column? I don't want to highlight the change, I want to be able to see if the date moved since last week and then calculate the number of days it moved (eg: +10) I don't wa…
    Answered - Pending Review
    26 1
    Answered - Pending Review 26 views 1 comment Most recent by Jennifer G.
  • VLOOKUP - want empty lookup result to allow free form typing
    In Formulas and Functions
    Hello, I'm using this formula: =VLOOKUP([Location Search]1, {Locations Range 1}, 2, false) where [Location Search] refers to a "LOCATION SEARCH" column in the sheet collecting the data and {Locations Range 1} refers to my look-…
    Answered - Pending Review
    11 1
    Answered - Pending Review 11 views 1 comment Most recent by Andrée Starå