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, In-Process, 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 multi-select 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 auto-update 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 = IT-HelpDesk 108 = IT-EnterpriseApplication…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 Jo-Ann Turning 13 views 8 comments 0 points Most recent by Jo-Ann 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 Functions138Jo-Ann 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 cross-sheet 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
