-
Custom Auto Numbering Function
Hi! New Smartsheet user here, appreciate any help and guidance. I'm looking to create a formula to build a custom auto-number function. I'm including a screenshot for reference. I'd like to automate the Project ID column. Where the project ID is YEAR - CLIENT - CLIENT PROJECT NUMBER. Is there a formula out there that would…
-
Using IF with more than 3 items
Hi, I gotten so much help here before, so once again... I have a column Total Rank Score where we assign points to each task. I want to be able to group those into 3 categories. If value is between 0 and 7 = "Low", if between between 7 to 10= Medium , If over 10 points ="High" =IF([Total Rank Score]@row <= 7, "LOW",…
-
Use a cell reference as a criteria in sumif and countif formulas
I have a set of data, as shown below, and I am using formulas to pull data from the columns nights, ADR and total. I am using COUNTIFS and SUMIS formulas to collect the data, which are working well. The issue I have is that I need to replicate this data and tables for multiple different buildings and units. Right now I am…
-
Integrate lookup list (vlookup?) with if statement
I have a lookup list of Councils and the towns they cover. On another sheet, I will be writing something like "the team will visit TOWN A". I want a formula to examine that statement and draw through the corresponding Council from the look up list. Can I write a formula in Smartsheet to achieve this, noting that there are…
-
Trying to create a formula that determines the current season
I've got a formula that generates the current quarter and one that calculates the current year. Now I'm trying to write an IF statement that creates a value that says S23 by reading if its Q1 or Q2 and then adding the 23 from the current year (2023). This is my current formula, can anyone explain why it's not working or…
-
COUNTIF or IF Statement
I am looking to add date columns that are based on a criteria in another cell. I can use the COUNTIF statement to add the dates but when I place an criteria in place I get an error. =COUNTIF([Due Date]:[Due Date], <=TODAY()) This works to count my dates that are less then or equal today but I need it also to filter out if…
-
How can I send automated updates to submitters?
I am setting up an automation for my client and am having trouble sending out automated messages and wondered if someone could help me with a solution. Here's the structure: Sheet 1: Reports are submitted monthly to sheet 1. (The submitters are not allowed to see the data in the workspace.) Approvers review the information…
-
Multi Nested IF/AND Formula
Hello, I really thought I could start out simple with this one and then build on it but I can't even get the first piece without an #UNPARSEABLE error. Here's a snip of the smartsheet columns I am comparing and the need is to return what Template to use. There's 10 different "Practice Group or Division" options but the…
-
What is wrong with this formula?
This formula has been working perfectly, but now I am getting an "Invalid Operations" error. What am I missing? =IF(OR(AND(Resolution@row = "BH: On-Hold", [Due Date]@row < TODAY()), AND(Resolution@row = "Correction: Need Follow-Up", [Due Date]@row < TODAY())), "No", IF(OR(AND(Resolution@row = "BH: On-Hold", [Due Date]@row…
-
How to add text from one formula to the result of another formula
Hello, I have two formulas. 1) calculates how many validated units there are. The result is 600. 2) calculates how many units there are in total. The result is 1200 I would like these formulas to combine so the result is 600 / 1200 I am aware I can use +" 1200" but this number will keep changing and the total number to…