Formula to flag required approvals and if they've been obtained
I'm new to Smartsheet and having some trouble with a formula and helper columns I am creating to display dashboard metrics differently, and to use for conditional formatting needs. The Reviews and Approvals Needed field is a dropdown with 4 approval levels and every project requires different approval combos.
I created the formula below for the Helper-CLT field that tells me if the project was approved or not based on CLT being required and status being a date:
=IF(AND(CONTAINS("CLT", [Reviews and Approvals Needed]@row:[Reviews and Approvals Needed]@row), ISDATE([Conservation Leadership Team (CLT) Review Status]@row)), "Approved", "Pending")
However, I can't figure out the order of a nested IF statement that would actually tell me: If CLT is required and CLT status is a date = "approved"; if CLT is required and CLT status is blank = "pending"; if CLT is not required, returns "N/A".
Is this possible? I keep getting unparseable as the result.
Best Answers
-
=IF(AND(CONTAINS("CLT", [Reviews and Approvals Needed]@row), ISDATE([Conservation Leadership Team (CLT) Review Status]@row)), "Approved", IF(AND(CONTAINS("CLT", [Reviews and Approvals Needed]@row), ISBLANK([Conservation Leadership Team (CLT) Review Status]@row)), "Pending", "N/A"))
If you found this comment helpful. Please respond with any of the buttons below. Awesomeπ€, Insightfulπ‘, Upvoteβ¬οΈ, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
My initial suggestion does leave some room open for issues, but assuming there will never be dates in the date fields that aren't "tagged", you could use something along the lines of:
=IF(COUNTM([reviews and Approvals Needed]@row) = COUNTIFS([Fist Date Column Name]@row:[Last Date Column Name]@row, ISDATE(@cell)), "Yes", "No")
The idea here is that we count how many approvals are needed and compare that to how many dates we have in the date columns. If those two numbers match, we are good to go.
Answers
-
=IF(AND(CONTAINS("CLT", [Reviews and Approvals Needed]@row), ISDATE([Conservation Leadership Team (CLT) Review Status]@row)), "Approved", IF(AND(CONTAINS("CLT", [Reviews and Approvals Needed]@row), ISBLANK([Conservation Leadership Team (CLT) Review Status]@row)), "Pending", "N/A"))
If you found this comment helpful. Please respond with any of the buttons below. Awesomeπ€, Insightfulπ‘, Upvoteβ¬οΈ, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
@Lauren Lederle When you are using @row you do not need to hyphen. As the range is literally the column in the same row your looking at. You only need to column:column if you are searching the entire column for some information
If you found this comment helpful. Please respond with any of the buttons below. Awesomeπ€, Insightfulπ‘, Upvoteβ¬οΈ, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Thank you Mark, that worked like a charm and I appreciate the tip!
-
You can also simplify this a little further like so:
=IF(HAS([Reviews And Approvals Needed]@row, "CLT"), IF([Conservation Leadership Team (CLT) Review Status]@row <> "", "Approved", "Pending"), "N/A")
When you find yourself repeating the same part in multiple AND statements, it can usually be simplified by using the first IF to specify the repeated portion of the AND and then a second IF to parse out the different pieces.
=IF(this is true for all instances, IF(this other thing is true, "true", "not true"), "first thing isn't true")
-
@Paul Newcome I would of recommended the same thing. How ever I did not just want to assume dates are going to be the only value in
Conservation Leadership Team (CLT) Review Status
If you found this comment helpful. Please respond with any of the buttons below. Awesomeπ€, Insightfulπ‘, Upvoteβ¬οΈ, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
@Mark.poole You could have done the same with the IF statement and just used the ISDATE function for the date itself. The main point of my post was to give another option when you have the same thing listed in multiple AND functions. The below will still remove the need for the AND functions and check for dates specifically. You will see it is the same overall structure.
=IF(HAS([Reviews And Approvals Needed]@row, "CLT"), IF(ISDATE([Conservation Leadership Team (CLT) Review Status]@row), "Approved", "Pending"), "N/A")
It still follows the same format for cutting out the ANDs.
=IF(this is true for all instances, IF(this other thing is true, "true", "not true"), "first thing isn't true")
.
-
Thank you both, I've learned so much from this thread and your comments on other threads as I was troubleshooting my issues this week.
-
@Paul Newcome I get what your saying now.
If you found this comment helpful. Please respond with any of the buttons below. Awesomeπ€, Insightfulπ‘, Upvoteβ¬οΈ, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
@Lauren Lederle Happy to help. ποΈ
@Mark.poole Awesome! It doesn't make a huge difference here because it is only two AND statements, but in instances where you have it across 5 or 6, it really cuts down on the typing.
-
@Paul Newcome and @Mark.poole, I have a follow up question if you are able to assist⦠Is there a formula that can create a yes/no symbol to indicate if all required approvals for a particular project have been obtained? In the screenshot below, I'm showing how I want the "All Approvals Received?" field to populate based on the "Reviews and Approvals Needed" tags and the status of approvals for each project. But I just cant figure out the formula needed. Thanks.
-
My initial suggestion does leave some room open for issues, but assuming there will never be dates in the date fields that aren't "tagged", you could use something along the lines of:
=IF(COUNTM([reviews and Approvals Needed]@row) = COUNTIFS([Fist Date Column Name]@row:[Last Date Column Name]@row, ISDATE(@cell)), "Yes", "No")
The idea here is that we count how many approvals are needed and compare that to how many dates we have in the date columns. If those two numbers match, we are good to go.
Help Article Resources
Categories
Check out the Formula Handbook template!