-
Changing of a date once it has passed
Hello Smartsheet community, I am looking for a formula where a cell automatically changes to the next date once the current day has passed. For example: If the date cell currently has a "12/3/2022", I want to replace it with next month's "1/3/2022" in the same cell. I am not sure if this would be an automation or something…
-
Countif or countifs
I have a column Titled Expiration date. I would like to count all the dates in this column if the date is before today's date. I was googling around and found something like this, but just cant get it figured out. Am I at least on the right track? =COUNTIFS([Expiration date]:[Expiration date], IFERROR(YEAR >=TODAY)
-
Need to add additional Criteria to Metric Formula
I have a Metrics Sheet (named "Escalation Metrics") where I could up all my metrics (most are words rather than numbers. so this allows me to easily create a dashboard based on data that does not contain numbers. My current formula is: =COUNTIF({Type of Escalation}, [Metric Name]@row) And I want to add the criteria that…
-
Replace semi colon with line break / carriage return in list of contacts
I need to import a file from a legacy system that has a list of emails separated by semi-colons in a cell. In order for the emails to be recognized as 'contacts' in Smartsheet I need to replace the ";" by a line break. What would be the easiest work around? Thanks,
-
Locking cells in sheet
Hello, I am trying to determine if there is a way to lock the formulas I have placed in my sheet so that the users cannot override them to hard key in numbers. The formulas have been entered into all of the necessary cells but as it stands, users can simply clear them out to enter the amounts in their own way. We have…
-
Getting #incorrect argument on IF formula
Help! I had this working then deleted the test lines and it deleted the formula also. Can anyone tell me what I'm missing here? =IF([Request Type]@row = "D&E Contract Signed", ([D&E Amount]@row * 0.3), IF([Request Type]@row = "Project Contract Signed", ([Estimated/Final Project Total]@row * [Estimated/Final Margin]@row *…
-
Using If formula with symbols
Hi there, Trying to create a formula using symbols, but am getting the #UNPARSEABLE message. Under my Progress column, I'm using symbols (Red, Green, Yellow and Blue). For the Value if true: if the symbol is green, then I want the number that is entered into the column titled "Run Time" to be populated into "Column 12".…
-
How to determine if a row id is in the list of predecessors for any row
Hello, I have a fairly large Project plan with 700+ rows and am new to Smartsheet. My predecessors list for any given task has multiple row IDs referenced (comma delimited) With help from Rob Hagan's post (https://community.smartsheet.com/discussion/formula-determine-row-number), I have created the ROW_ID) column for…
-
Vlookup / @row usage
In the Vlookup formula, Category@row references 4 contiguous columns to obtain one value, I added a 5th column and Category@row does not recognize the 5th column. How do I redefine Category@row to use the 5th column ? VLOOKUP(Category@row, {GL Account X Ref Range 4}, 2, false) Thanks for your help! Amy
-
Combine Index Match with SumIFS (Multiple Criteria)
Hello, I am having trouble figuring out how to calculate a sum if multiple criteria are met. I am referencing data between two sheets. I want to test if there is a match for two criteria in 'Sheet 1' (Project Number and Labor Code) against those same criteria in 'Sheet 2'. If both criteria are met, I would like to sum all…