Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Smartsheet formulas with multiple columns
We're using Smartsheet for some change management, and I'm new to both Smartsheet and spreadsheet formulas.
What I’d like to do is have a formula looks at a column that indicates the type of change (Major, Minor, Emergency, etc.) and then looks at a number of check boxes and adds an approval in a separate column.
So, for instance, if it’s an emergency change, then only one approval is necessary. There are 6 people on the CAB, so I’d want it setup so if the change type is emergency, then as long as at least one box is checked, the change is approved. If it’s a Major change, then 3 or more check boxes would need to be checked.
If I'm thinking correctly, it would work like this: change type column determines number of check boxes necessary, a formula counts the number of boxes checked (0-6) and then determines whether a sufficient number of boxes have been checked to change a separate column to "approved."
Comments
-
I believe this is what you're asking for. An "Approval" column checks how many Checkboxes are checked with the type of change. I've used a countif to count the number of sign checkboxes.
Counter column counts the number of sign checkboxes.
Counter Column Formula:
=COUNTIF([Sign 1]2:[Sign 3]2, 1)
Nested if statement to compare the number of sign checkboxes with the counter.
Approval Column Formula:
=IF(AND([Change Type]2 = "Emergency", Counter2 >= 1), "Approved", IF(AND([Change Type]2 = "Minor", Counter2 >= 2), "Approved", IF(AND([Change Type]2 = "Major", Counter2 >= 3), "Approved", "Not")))
I'd also recommend using a dropdown box in the change type to make sure an odd type, or typo doesn't mess things up.
Good Luck
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives