-
INDEX MATCH with IF function
Hello smartsheet community, I am stuck! The below shows SHEET 1. There is an automation on this sheet that copies all rows on the 27th of the month to SHEET 2. I then want to pull the data from the load column back from SHEET 2, to SHEET 1. As the 'Date Added' column has a new month every month, the data will be pulled…
-
Nested IF Function using Harvey Balls
Hi SS Community, I have the following nested IF column formula . The formula works properly through the "Red" part, but once I attempt to type DECLINED in the "Revised Final Date" column, the formula returns #invalidoperation. What can I change in my formula to show a Gray Harvey ball when I type DECLINED in the "Revised…
-
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?…
