-
Date calculations based on frequency
I am struggling to get a formula to work. I am trying to write a formula that will calculate the next date a review will need to take place based on a manually entered date for when the last review was done and the frequency in which the review needs to take place for that row. The review frequencies are Monthly,…
-
how to get the circle quarter symbols to change colour???
Ok so I am using the below symbols with a formula in background to be in different qtrs/ stages of a project.... Background Formula is: =IFERROR(IF([%]@row <= 0.01, "Empty", IF([%]@row < 0.26, "Quarter", IF([%]@row < 0.51, "Half", IF([%]@row < 0.76, "Three Quarter", "Full")))), "") However, I want to change the colour as I…
-
Formula Help
Hello Just wondering if someone could help me update this column formula. I need it to exclude the N/A status. It is currently a column formula column that flags any items that are past due from the End Date column. =IF(AND([End Date]@row < TODAY(), NOT(ISBLANK([Program Status Type]@row)), NOT(Status@row = "Complete")), 1,…
-
Count months in another sheet but only within a certain year
I have one sheet with dates listed (ex: 9/10/21) that range from 2020, 2021, and 2022. I'm using this formula to count how many are in each month: =COUNTIF({Head And Neck_Project Overview Range 1}, IFERROR(MONTH(@cell), 0) = 1) But I'd like to break it down by year so I'd like to find how many are in January of 2021 only.…
-
Create SUMIFS formula with a contact criteria in a multiple contacts range
Hi, I am looking for the right way to build my formula that makes a sum of a number column with a contact criteria in a multiple contacts range. Example: =SUMIFS({numberRange with hours}; {Criteria Range with multiple contacts}; Contact Name; {Range with Statuscode}; "In Progress") Check also the screenshot below. If I…
-
Create unique list that populates a second sheet (not report)?
Hello, I'm having trouble figuring out the correct formula to populate a list of unique locations and totals their entries on a second sheet. I couldn't find a forum example that doesn't use a report. This needs to be on a second sheet because it will be used in other formulas and dashboards. Sheet 1 includes a list of…
-
Create multi-contact cell from a row array of individual contacts
I have a sheet as: Header: SME1; SME2; SME3; All SMEs Row 1: Bill Ross; Suzie Collins; Anne Doe; =join([SME1:SME3]@row) Each of the SME columns is a contact. I want to fill my 'All SMEs' column with a formula which makes a multi-contact with all of the contacts joined together for easier filtering. I've tried to write the…
-
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 *…
-
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…
