Formulas and Functions
Archives for July 2020
Discussion List

Yielding blank cellsQuestionAnswered  Pending Review Esperanza Zagal 13 views 2 comments 0 points Most recent by Esperanza Zagal Formulas and FunctionsFirst post 07/31/20,Last post 08/10/20Hello, I'm using health circles to indicate progress. However, I want the circles (green, red, yellow, and gray) to show active stages in the process (complete, incomplete, in progress, on hold, respectively). I want phases that are not y…Formulas and Functions132Esperanza Zagal

Help with a SUMIF formula and a reconciliation spreadsheetAccepted AnswerAccepted Answer Mark Silvester 22 views 9 comments 0 points Most recent by Genevieve P Formulas and FunctionsFirst post 07/31/20,Last post 08/06/20I am trying to build a reconciliation spreadsheet that compares two sets of data. Pictured below: (Image) The first set of data with the "QBO SKU" is pulling the information correctly using the below formula: =SUMIFS({QBO – sh…Formulas and Functions229Mark Silvester

Count IF for fixed datesQuestionAnswered  Pending Review brian25736 1 view 1 comment 0 points Most recent by Sean Morgan Formulas and FunctionsFirst post 07/31/20,Last post 08/01/20I am trying to do a simple CountIF, where the 'if' is whether an Expiration Date field is between a certain range, for example for if Expiration Date in in = January 2020, I would want to count any expiries between 1/1/2020 and 1/31/2020..…Formulas and Functions11brian25736

#INVALID REF for WidgetQuestionAnswered  Pending Review Jamie Wisegerber 14 views 7 comments 0 points Most recent by Jamie Wisegerber Formulas and FunctionsFirst post 07/31/20,Last post 08/07/20Hi Community. I'm hoping someone can help me out. I created a task tracking sheet. A general rule is that our reporting month is always two month behind today. In my sheet summary I have a formula using index match, which works perfectly t…Formulas and Functions147Jamie Wisegerber

Counting Parents for a Specific YearAccepted AnswerAccepted Answer MichaelSmith1726 11 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/31/20,Last post 07/31/20Our organization receives requests for work and some of these requests may have multiple actions that need to be completed. I have the additional action(s) indented under the first action (request) to try and display that this one request …Formulas and Functions113MichaelSmith1726

Answered  Pending Review Dylan O'Brien 12 views 1 comment 0 points Most recent by SoS  Dan Palenchar Formulas and FunctionsFirst post 07/31/20,Last post 07/31/20Hello, I am trying to get list of values that has each value only once. Below I have my JOB NUMBER column and I am trying to create an automatically updating column that we will call DISTINCT NUMBER that lists each number once. The catch…Formulas and Functions121Dylan O'Brien

Multiple "OR" for single formula?Accepted AnswerAccepted Answer Travis Griffin 22 views 8 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/31/20,Last post 07/31/20I have about 14 single choice options in a column that if chosen, lead to a specific Harvey ball status. I've used multiple "Or" entries, and I wonder what I'm leaving out, as it's coming back unparceable. Here's the formula: …Formulas and Functions228Travis Griffin

Symbols QuestionQuestionAnswered  Pending Review Amy Evans 12 views 5 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/31/20,Last post 07/31/20Hello, I'm receiving an "Unparseable" error on the below. I have a column where I selected symbols to display different statuses. I selected the colored circles where it is Green, Yellow, Red and Grey. Is this issue because I did…Formulas and Functions125Amy Evans

Answered  Pending Review Deanna Vandermeer 5 views 3 comments 0 points Most recent by Deanna Vandermeer Formulas and FunctionsFirst post 07/31/20,Last post 07/31/20At the moment, I have a formula that is not actually doing what I need it to do. Basically what I have is an inventory sheet and when folks remove from the inventory I need it to look at the cost of that item from the cost column in that s…Formulas and Functions53Deanna Vandermeer

Formula "Project is not complete and due day is passed"Accepted AnswerAccepted Answer Tamara Kuligina 11 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/31/20,Last post 07/31/20Hi everybody, could you help me with a formula please: The Idea is if the Status of the project is not "Complete" and due day is passed (it's more then TODAY), i need the red symbol in the column. In other cases, i need nothing. …Formulas and Functions113Tamara Kuligina

Answered  Pending Review Matthew Smith 21 views 4 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 07/31/20,Last post 07/31/20Hello, my name is Matthew, and I am working on a project that gathers data from clients. The goal is to receive documents from them and have them uploaded to SmartSheet. I would like to be able to send them a form based off a contact cell,…Formulas and Functions214Matthew Smith

Answered  Pending Review JARED AMOTH 11 views 5 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/31/20,Last post 07/31/20I'm attempting to insert a formula into parent rows that assign a % complete based on entries on child rows. The child rows responses are locked based on task completion. The options are: "Yes, InProcess, and Not Started." I've …Formulas and Functions115JARED AMOTH

Multiple IF or MatchQuestionAnswered  Pending Review EMA 11 views 6 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/31/20,Last post 07/31/20Have a scenario that I want say if column A matches Column B, then enter value column C, If Column A matches, Column D, then enter value column E, If Column A matches Column F, Then enter value column G. Is this better done through some so…Formulas and Functions116EMA

Answered  Pending Review Michele Carr 16 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/31/20,Last post 07/31/20I have some very basic Conditional Formatting rules based on a System Date "Submitted". They were working fine, but today they just stopped working. I have attached a screen shot of the rules. Any idea why this would suddenly sto…Formulas and Functions163Michele Carr

Answered  Pending Review Jamie Bedford 22 views 4 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 07/30/20,Last post 08/11/20Hi there, Is it possible to add hyperlinks to each option in a multiselect column? For me this is less important in a Sheet than in a Report (or Dashboard), but I presume this would need to be set up in a Sheet for it to flow to a Report …Formulas and Functions224Jamie Bedford

Adding formula to existing formula for status changeAccepted AnswerAccepted Answer Nathan Nguyen 11 views 2 comments 0 points Most recent by Nathan Nguyen Formulas and FunctionsFirst post 07/30/20,Last post 07/31/20Hi everyone, I currently have a formula in my status column that autoupdate depends on the percentage completion. Anything that is 0 is Not Started, and greater than 0% and less than 100% is Green (G), and anything that is 100% is Complet…Formulas and Functions112Nathan Nguyen

AddAccepted AnswerAccepted Answer dskpetersen 12 views 2 comments 0 points Most recent by dskpetersen Formulas and FunctionsFirst post 07/30/20,Last post 07/31/20I have 3 Sheets: #1 = Closeouts, #2 = Annual Incentives; #3  Incentive Distribution When a closeout is completed, data is entered on Sheet#3  Inc. Dist. I have a Data Mesh to pull the data from the # 3 to populate Info on #2 Ann Inc.…Formulas and Functions122dskpetersen

Answered  Pending Review Evan Summerville 13 views 2 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/30/20,Last post 07/31/20I'm attempting to create a formula that will translate Start Date into sprint number. In my spreadsheet there is a column for Sprint that I'd like to populate it with Sprint 1, Sprint 2, Sprint 3, etc based on date. For example Sprint 1's …Formulas and Functions132Evan Summerville

Multiple IFs Statement: Number to NameAccepted AnswerAccepted Answer Evan Summerville 14 views 4 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 07/30/20,Last post 07/31/20We currently use numerical codes to denote our teams that are synced from JIRA. Those numbers are reflected in a column which I'd like to translate into the relevant team names. Team Column: 107 = ITHelpDesk 108 = ITEnterpriseApplication…Formulas and Functions144Evan Summerville

Answered  Pending Review Chris Patterson 2 views 1 comment 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 07/30/20,Last post 07/30/20For example, I am using cell linking to pull in two different large pieces of data from two separate sheets. What I'm trying to resolve is how to flag column 3 date if it does not match column 2 date. I want to highlight the dates that d…Formulas and Functions21Chris Patterson

Does anyone know why the COUNTIFS function used with a DATE range stops counting things done at 7PM?Accepted AnswerAccepted Answer Avery Rueckheim 33 views 12 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/30/20,Last post 08/03/20I am currently using this function: =COUNTIFS({Closed}, <=DATE(2020, 4, 30), {Closed}, >=DATE(2020, 4, 1)) to calculate the number of projects closed every month. When I compare the number that is calculated to the number of rows…Formulas and Functions3312Avery Rueckheim

How do I resubscribe a user?Accepted AnswerAccepted Answer Dina Gerbasio 14 views 4 comments 1 point Most recent by Dina Gerbasio Formulas and FunctionsFirst post 07/30/20,Last post 08/03/20I have a smartsheet set up for forms on our website and I created a workflow that sends to a specific email address whenever a new line is added. They contacted me to say they were not receiving the notification emails so when I checked t…Formulas and Functions144Dina Gerbasio

COUNTIFS based on conditionsQuestionAnswered  Pending Review Sarah Lanahan 24 views 6 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/30/20,Last post 08/10/20Hi I'm trying to count the number of teachers from different cohorts that have not yet given feedback through a form, so the cells with #NO MATCH. I have all of the data in 1 SS and am totally and running metrics in another. In the sheet…Formulas and Functions246Sarah Lanahan

Need help with FormulaQuestionAnswered  Pending Review anilkrag77641 21 views 2 comments 0 points Most recent by anilkrag77641 Formulas and FunctionsFirst post 07/30/20,Last post 07/31/20Hello I am not an expert in automation, So need your assistance in mapping one data. I have requirement wherein in one Smartsheet I have values in 3 columns and on the other Smartsheet I have all the values present 3 columns in Sheet 1 is…Formulas and Functions212anilkrag77641

RYGB and Check BoxQuestionAnswered  Pending Review Leslie Lupo 11 views 1 comment 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 07/30/20,Last post 07/30/20Hello everyone, I am a new Smartsheet user and having problems with a function. When the "Blue" ball is selected in the Health column, I would to automatically have the check box in the Complete column checked. Embarrassed to ask…Formulas and Functions111Leslie Lupo

Answered  Pending Review JoAnn Turning 13 views 8 comments 0 points Most recent by JoAnn Turning Formulas and FunctionsFirst post 07/30/20,Last post 08/03/20I am not sure what is wrong with my very simple formula... =COUNTIF(Quarter:Quarter, "Q1") I have a list of 700 line items and am trying to add formula to total up a count of programs 'by quarter' in sheet summary and keep getti…Formulas and Functions138JoAnn Turning

Accepted Answer Chris Hockey 12 views 5 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/30/20,Last post 07/30/20I am trying to count the number of instances that certain text occurs across columns in a sheet. How do I count the number of "F"s, and "P"s across these columns. (Image)Formulas and Functions125Chris Hockey

Cross Sheet Formula Limit ErrorQuestionAnswered  Pending Review Matt Metzger 18 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/30/20,Last post 07/30/20I'm currently getting an error related to the crosssheet formula limit. I'm not sure why I'm getting the error. So far I have referenced 6 columns in other sheets with SUMIFS formula. I received the error when I added the 2019 formula …Formulas and Functions183Matt Metzger

How do I do a row request?QuestionAnswered  Pending Review Joshua Bruck 1 view 1 comment 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/30/20,Last post 07/30/20I am trying to create groups of tasks that can be used by using a filter. Unfortunately I need to be able to know the row# of all added rows in order to have the new rows visible for each group. I want to have a cell that marks the start o…Formulas and Functions11Joshua Bruck

Formula most urgent topics with RYG, dateAccepted AnswerAccepted Answer Jörg Schmidt Novum Hospitality 13 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/30/20,Last post 07/30/20Hallo, I want to combine the following columns with a formula in the sheet summary: RYG, date of receipt, comment and hotel The sheet summary should show "hotel name" and "comment" if RYG = "red" and (the e…Formulas and Functions133Jörg Schmidt Novum Hospitality