Formulas and Functions

Automate At Risk flagAccepted Answer

Accepted Answer Cara Sikes 16 views 3 comments 0 points Most recent by Paul Newcome 03/26/20

First post 03/26/20,Last post 03/26/20
I am very new to Smartsheet - trying to figure this out and could use some help. I need to have my At Risk flag marked automatically if a task is due [Complete By] within three days and the [% Complete] is not 100%. The one I am currently …
Formulas and Functions163
Cara Sikes
Document the value of function / calculated field every dayAccepted Answer

Accepted Answer Stefan Milic 18 views 3 comments 0 points Most recent by Paul Newcome 03/26/20

First post 03/26/20,Last post 03/26/20
Hi all, I have 2 sheets: 1) working sheet where all tickets are logged + their status is maintained 2) calculation sheet with fields showing e.g. number of open / closed tickets and so on (calculated by formulas which refer to the working…
Formulas and Functions183
Stefan Milic
Dynamics metrics across new sheetsAnswered Question

Answered BHannah 16 views 1 comment 0 points Most recent by Paul Newcome 03/26/20

First post 03/26/20,Last post 03/26/20
Goal: create metrics for a dashboard that automatically update when new project plans are created in the same workspace. For example: KPI=Number of late deliveries across all projects at a client I can create a metrics sheet and create a …
Formulas and Functions161
BHannah
Automating Status Balls to Showcase Overall HealthAnswered Question

Answered Shay Baumhauer 28 views 2 comments 0 points Most recent by Susie Wachowski 03/26/20

First post 02/13/20,Last post 03/26/20
Hey There, I am hoping to find a formula that will show the g/y/r status balls "overall health" based on the percentage of green, yellow or red balls in the column. Is there a way to "average" that? For example, if we …
Formulas and Functions282
Shay Baumhauer
Counting people with the same email domain in a multiselect assigned to cellAnswered Question

Answered Martina Barnett 20 views 7 comments 0 points Most recent by Paul Newcome 03/26/20

First post 03/25/20,Last post 03/26/20
Hi there I have a multiselect Assigned To column linked to a contact list. So the contacts are displayed with their full names. Email addresses are shown in the hover over menu for each contact as per usual. I want to count how many of t…
Formulas and Functions207
Martina Barnett
Help with a Countifs formula with conditionsAnswered Question

Answered Mommaduck 39 views 23 comments 0 points Most recent by Paul Newcome 03/26/20

First post 03/19/20,Last post 03/26/20
We have a sheet that holds information populated by a form. The form allows people to request the data team create a report for them. Sometimes the requests are actually looking for a report that would be regenerated every month. The in…
Formulas and Functions3923
Mommaduck
Trying to count values based on variablesAccepted Answer

Accepted Answer Acorn 13 views 2 comments 0 points Most recent by Acorn 03/26/20

First post 03/26/20,Last post 03/26/20
Hi there - first question ever! Also - not great at using the right language so sorry if it's a bit confusing! I am currently trying to use a formula to count if a certain condition is met in column a - and in column b - to count it. so f…
Formulas and Functions132
Acorn
Formula to automatically tick the checklist box if the status has been marked as "completed"Accepted Answer

Accepted Answer Leah Mayes 11 views 4 comments 0 points Most recent by Andrée Starå 03/26/20

First post 03/26/20,Last post 03/26/20
Hi all Is there an IF statement or a formula that can do the above? Rather than manually ticking the box Thanks :)
Formulas and Functions114
Leah Mayes
Populating a value from one sheet (not just using the VALUE formula)Answered Question

Answered Lauren Garvey 12 views 15 comments 0 points Most recent by Genevieve P 03/26/20

First post 03/25/20,Last post 03/26/20
Hi I'm not sure if what I am looking for is possible, but wanted to check. What I am looking to do is populate specific cell information from one sheet to another with select row information, when a column value is yes. Example sheet that…
Formulas and Functions1215
Lauren Garvey
Determining Final Approval StatusAccepted Answer

Accepted Answer Robert Francher 12 views 4 comments 0 points Most recent by Robert Francher 03/26/20

First post 03/26/20,Last post 03/26/20
I have an approval flow that can go to one of three contacts (sometimes two). I want to add a Final Status column to make it easier to count in a summary sheet and eventual put it on a dashboard. I'm trying to create a formula with the f…
Formulas and Functions124
Robert Francher
Index Match Formula help neededUnanswered Question

Question Rainier Hollands 22 views 0 comments 0 points Started by Rainier Hollands 03/25/20

First post 03/25/20,Last post 03/26/20
I'm currently trying to calculate the fuel consumption for each truck by date by using using an Index Match formula as shown below =INDEX(Odometer:Odometer, MATCH(MAX(COLLECT(Created:Created, Created:Created, @cell < [email protected], Truck:…
Formulas and Functions220
Rainier Hollands
How to create 2-way sharing across sheets?Answered Question

Answered Esperanza Zagal 12 views 2 comments 0 points Most recent by Paul Newcome 03/26/20

First post 03/25/20,Last post 03/26/20
Hi, I am trying to make certain cells accessible for 2 way sharing. Is there a function like cell-linking that is available 2-ways? There is some financial data but mostly personnel/task and resources. Thank you, Esperanza
Formulas and Functions122
Esperanza Zagal
Automated emails lookAnswered Question

Answered Amanda Ericksen 14 views 2 comments 0 points Most recent by Paul Newcome 03/26/20

First post 03/25/20,Last post 03/26/20
Hello, I have my sheets set up right now to automatically send out an email with an attachment when a box is checked. I have received complaints that the emails look to formal and or spam like. Especially when the sender is "Your n…
Formulas and Functions142
Amanda Ericksen
Formula error on Duplicate date checkAccepted Answer

Accepted Answer HOFF 28 views 3 comments 0 points Most recent by Paul Newcome 03/26/20

First post 03/23/20,Last post 03/26/20
Have found this formula in previous discussions but is unparseable. I have gone through it a couple of times and think it is down to wrongly set parentheses, or? Slutdato=enddate, Startdato=start date. Looking to flag (double booking) if s…
Formulas and Functions283
HOFF
How to get a lookup (Vlookup or Index Match) to return multiple values?

gregoc43 276 views 8 comments 0 points Most recent by Paul Newcome 03/26/20

First post 10/23/18,Last post 03/26/20
I'm essentially trying to have one cell populate with text values from multiple different cells based on a certain criteria. What I really wish I had is a JOINIF function, if that explains my problem better. Thanks in advance!
Formulas and Functions2768
gregoc43
Date updating to Next Day using Index, Match, Max, Collect FormulaAnswered Question

Answered Amy Arnold 32 views 3 comments 0 points Most recent by Debbie Sawyer 03/26/20

First post 03/20/20,Last post 03/26/20
Hi, I'm working with two Smartsheets. (1) One sheet collects the data from a Smartsheet form. I added two columns to this sheet using the Auto-Number/System Feature (under Edit Column Properties). This features stamps new row entries wit…
Formulas and Functions323
Amy Arnold
How can I convert YYYYMMDD to a SmartSheet date value?Accepted Answer

Accepted Answer Anirudh 16 views 2 comments 0 points Most recent by markmontour-larson102391 03/25/20

First post 03/25/20,Last post 03/25/20
I have about 2500 date values, imported from an Excel spreadsheet, in the YYYYMMDD numerical format. I would like to convert them to Smartsheet date values. Any suggestions?
Formulas and Functions162
Anirudh
#INVALID COLUMN VALUE with COLLECT FunctionAccepted Answer

Accepted Answer coryg 67 views 9 comments 0 points Most recent by Paul Newcome 03/25/20

First post 01/03/20,Last post 03/25/20
I am attempting to create formulas to return the next milestone date and next milestone description from a project schedule. Here is what I have currently: https://us.v-cdn.net/6031209/uploads/413/NNXTNAMADMEG.png As you can see, the next …
Formulas and Functions679
coryg
formula for daily countAnswered Question

Answered rymurph 13 views 3 comments 0 points Most recent by Andrée Starå 03/25/20

First post 03/25/20,Last post 03/25/20
I'm looking to count how many times "blank" occurs in the status column per date. Entries are made daily and some days have multiple entries. I would like to set up a way to get a count of number of entries per day.
Formulas and Functions133
rymurph
Data mesh from published reportAnswered Question

Answered Maria Fernanda Lollato 37 views 2 comments 0 points Most recent by Maria Fernanda Lollato 03/25/20

First post 03/05/20,Last post 03/25/20
I am trying to set a data mesh config of data coming for a published report (which I do not have access to the source sheet), ans it seems that he config is not able to read the common values. Could it be because the source sheet is not sh…
Formulas and Functions372
Maria Fernanda Lollato
Time Formats - Number formats - Rethinking the UX and function

Ezra 147 views 11 comments 0 points Most recent by Maria Fernanda Lollato 03/25/20

First post 04/01/19,Last post 03/25/20
Having an entire dialog for text and number formats would make much more sense to me.. combine the currency, percentage, thousands and decimal places into it's own dialog box. We really NEED a way to save and modify the formatting of comm…
Formulas and Functions14711
Ezra
List of csv in a cell must match a rangeAnswered Question

Answered Miriana Dounis 12 views 1 comment 0 points Most recent by Genevieve P 03/25/20

First post 03/24/20,Last post 03/25/20
I have a sheet with a cell in each row that contains a list of comma separated values. I want to evaluate this list against a a different sheet (sheet 2) and return true if the cell contents matches one of the columns of values in sheet 2.…
Formulas and Functions121
Miriana Dounis
Counting Duplicates using a FormulaAccepted Answer

Accepted Answer KMan1190 20 views 3 comments 0 points Most recent by Paul Newcome 03/25/20

First post 03/24/20,Last post 03/25/20
Trying to use COUNTIF to count how many times the values listed in Name Helper are listed in that column. IE see screenshot below. Id like a formula to automatically display the number 1 in the Column called "How Many Active Episodes…
Formulas and Functions203
KMan1190
Multiple IF statementsAccepted Answer

Accepted Answer Katie Mueller 15 views 4 comments 0 points Most recent by Katie Mueller 03/25/20

First post 03/25/20,Last post 03/25/20
I am trying to get the following to happen, and i am newer to smartsheet so need some help please. I need that if the checkbox in 'row 2' (and under based the amount of entries) is checked AND the Lessons Complete in the first row is starr…
Formulas and Functions154
Katie Mueller
Need a formula that will send out a reminder if an update has not been received in 7 daysAnswered Question

Answered Barb Celic 13 views 1 comment 0 points Most recent by jpagan03101416 03/25/20

First post 03/25/20,Last post 03/25/20
Hi all, I'm pretty new to smartsheet, so thanking you in advance for any assistance you can provide. I'm looking for a formula that will send out a reminder if an update request has not been received after 7 days. Here's the scenario. I ha…
Formulas and Functions131
Barb Celic
formula questionAnswered Question

Answered sahilhq 10 views 3 comments 0 points Most recent by Andrée Starå 03/25/20

First post 03/25/20,Last post 03/25/20
Hi, Please can you assist with a formula for the attached image. So if the induction column has "Yes" and Presentation column has "Yes" then the complete column should state "Yes". I'd like this formula to be…
Formulas and Functions103
sahilhq
vlook upAnswered Question

Answered sahilhq 9 views 1 comment 0 points Most recent by sahilhq 03/25/20

First post 03/25/20,Last post 03/25/20
Hi, I'd like assistance in creating a vlook up formula. So I would like the sheet to vlook up and find the date of birth and postcode from a different sheet when the name matches. See attached image of how the sheet where the vlook up nee…
Formulas and Functions91
sahilhq
Nested IF giving me an Unparseable errorAnswered Question

Answered Jennifer Tillman 61 views 17 comments 0 points Most recent by Jennifer Tillman 03/25/20

First post 02/27/20,Last post 03/25/20
I am trying to create buckets of product go-live dates using the Nested IF formula. When I only had 4 buckets, it worked perfectly. When I added 3 more buckets, I cannot get it to work. Here's the formula: =IF([Workdays to Go-Live Targe…
Formulas and Functions6117
Jennifer Tillman
Having trouble with the duplicate formula and where to input.Accepted Answer

Accepted Answer Rebecca Lane 18 views 3 comments 0 points Most recent by Paul Newcome 03/25/20

First post 03/24/20,Last post 03/25/20
https://us.v-cdn.net/6031209/uploads/031/I7CRYLLA49U6.png I am trying to set-up a formula, so if there is a duplicate in the Family ID column, the box becomes checked in the Duplicate column. So far, during the searching I have done for o…
Formulas and Functions183
Rebecca Lane
Link Data in a Smartsheet on a Specific Day and Make StaticAnswered Question

Answered Donna T 8 views 2 comments 0 points Most recent by Donna T 03/25/20

First post 03/25/20,Last post 03/25/20
I have several 13 Smartsheets that make up a campaign of projects. At the top of each sheet I have some metrics, including the % complete for all the projects, combined, on the Smartsheet. I want to create a new Smartsheet that links dat…
Formulas and Functions82
Donna T

Formulas and Functions

Formulas and Functions

Discussion List