-
How to Write IF Statement for a Symbol Column
I have a Symbol column that I'd like to auto-change to Green, Yellow, or Red depending on the date values in my Start, Finish, and Project Status columns. For instance: If Start is in the past and Finish is in the future and Project Status is not "Complete" or "In Progress", Symbol changes to Yellow. I really wish…
-
JOIN IF formula help!
I'm looking to Join 2 contact columns into 1 text column* with something to space the 2 values out so the names in the formula column do not run together w/out spaces. The problem with this is that if there is a missing value in 1 or both reference columns, the character I'm using to space (" | ") appears for no reason.…
-
Countifs: Help with syntax in Countifs Formula with multiple conditions
Hello All, I am trying to return a count when 2 conditions are present from a referenced smartsheet. This is the formula I created according to the guides I can find, but smartsheet says it is unparsable. Can someone let me know what the error is? =COUNTIFS({Range 3}, "Current", {Range 2}, "Critical")
-
Date formula working for half of rows
I have a columns for expiration date, 6 months prior to exp date, and 3 months prior to exp date. Formula for 6 months prior is: =IFERROR(IFERROR(DATE(YEAR([Expiration Date]@row), MONTH([Expiration Date]@row) - 6, DAY([Expiration Date]@row)), DATE(YEAR([Expiration Date]@row) - 1, MONTH([Expiration Date]@row) + 6,…
-
Nesting Formulas
Thanks in advance for any help! Doing a gantt with dependencies turned off and want my % complete column to calculate the percentage along with an IF statement so that start dates in the future return a 0% complete answer and finish dates in the past return a 100% complete answer. I know how to do each of them individually…
-
How can I collect a prior score based on 2 criteria?
We do 5S Audits of our building and I need to be able to pull the prior two scores of the same area. It worked for a while but then all of a sudden it started showing the Divide by Zero Error even though there are prior scores. Prior Score Formula: We got the prior 5S Date to pull but for some reason, we can't pull the…
-
VLOOKUP or INDEX MATCH Referencing Another Sheet
I would like to pull "Type" from a different sheet by matching the Project #. I've used both INDEX MATCH and VLOOKUP formulas which has worked well! Here are the formulas: =INDEX({Program Intake Form Range 1}, MATCH([Project #]#, {Program Intake Form Range 2}, 0)) =VLOOKUP([Project #]#, {Program Intake Form Range 2}, 5,…
-
Formula using a checkbox column to get Yes/No
Greetings, I'm trying to write a formula that will give me a yes or no in a new column when criteria is meet. For example, if in Services Needed column contains HVAC, Lock/Unlock, Lighting Only AND HVAC Completed, Lock/Unlock Completed, Lighting Only Completed are all checked then it will equal Yes in all services done?…
-
Enable move of formula editor window
Hello awesome people, Especially when working with large, complicated formulas, the color coding of the variables and corresponding cells is really helpful! (And shoutout also for making it so that when I paste formulas into onenote to work with them, the color coding remains. Great feature.) Unfortunately, the formula…
-
Uncheck Box
I have a formula that says IF(Date@row<TODAY(-14),1,0), to add a checkmark, but how do I add a formula that says once a next recurring date has been added to remove this checkmark.
