-
Formula for Finding ISBN Numbers
I wanted to share a Formula / Method for validating ISBN numbers since I didn't see any discussions or suggestions on how to go about, I thought I would make a post here for if anybody wanted it or had discussion ideas on it. I was recently tasked with creating a book request form where users would need to submit ISBN…
-
Return a value using Index/Match having multiple matching criteria
Hello Smartsheet Community, Does anyone know if we are able to return a value using the Index/Match but match two criteria? I have one sheet that is searching in the other sheet for two values in two different columns and will return a value in the third column. It will only return the value if the other two values match.…
-
Formula and Color Change in Cell
Hello, I am wondering if it was possible for the "Initial Risk Score" cell color to change when the "Probability" and "Severity" is selected? I currently have the following formula in all cells "Initial Risk Score" =IF(AND(Probability1 = "Improbable", Severity1 = "Negligible"), "1", IF(AND(Probability1 = "Improbable",…
-
Can I use 2 formulas in 1 cell/column?
Hello! I'm stuck trying to put to formulas into one column. Here's the scenario. I have multiple projects with multiple subtasks. When the Status column is updated to "Completed" I would like the Progress column to update to 100%. I've successfully completed this step with the formula: =IF(Status1 = "Completed", "100%") in…
-
Merged: Automation Action to calculate a Variable Value
This discussion has been merged.
-
How do I write a formula that adds "+" if the sum of two columns is more than 1
I have an anticipated finish date column and an actual finish date column. I have a formula to subtract the actual finish from the anticipated so that I can determine the variance. If for example the actual finish was 6 days longer than the anticipated, I want it to read "+6" and not just "6" (how it shows now). How do I…
-
Fiscal Week (in a Quarter) based on a Date Formula
Hi, I am struggling putting together a forumla that would calculate Fiscal Week (13 FW in a FQ) based on a date and taking in consideration that our FY starts February 1. Any help/advice would be appreciated! Nataša
-
How to count the number of times a value appears based on multiple criteria in another column?
Hello, I am trying to create a formula that counts the number of times an email appears based on a number of selections made from a drop-down menu. The process is for counting the number of trainings completed per quarter per employee. The trainings are submitted through a form and only one training title can be submitted…
-
Problems with Sumif formula
Hi, I'm trying to work out what's wrong with this formula. Any help appreciated. Thanks =SUMIF({Plan Metrics Range 2},(HAS(@CELL),[Work Stream Members]@row),{Plan Metrics Range 1}) Plan Metrics Range 2 is a column in a calculations sheet which has a list of team members across multiple teams (contact list) Work Stream…
-
IF & Vlookup
Hi there, I am trying to complete a VLOOKUP with an IF Statement but its returning a blank despite data being available in the cross-reference sheet =IF([Ex Works]@row = "Yes"; VLOOKUP([Product Number & Name]@row; {Item Master Range 1}; 3; VLOOKUP([Product Number & Name]@row; {Item Master Range 1}; 2))) What am I doing…