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.
If/Then Statement
What would be the best forumla to have one cell change status based on multiple other cells? For example, I have a sheet with four columns.
I need a formula that won't change Fake Company 1 " Contract Closed to "yes" until all Lower Tier Vendor's associated with Fake Company1 have "yes" under Contract Closed.
Comments
-
Hi Kevin,
I would approach your problem by first rearranging the data so that you put the names of Fake Companies 2 through 5 in as Vendor Name and renamed Lower Tier Vendor as Upper Tier Vendor (and put "Fake Company 1" in for each of these subordinates). In effect, you then have a database of companies where the subsidiaries have the name of their parent, and a blank parent means that you are at the top of this two level hierarchy.
You can then count the number of "No" values in the Contract Closed column where the Vendor Name is equal to the Upper Tier Vendor, and that is your answer.
I would use COUNTIFS for this (see Smartsheet Formula Examples which you can obtain by creating a new sheet from the available templates in your SmartSheet). Setting up these formulas is fun and you can learn a lot on the way...
Cheers,
Rob.
-
Kevin
Are you looking for a formula which call be semi-intelligent and work without a predefined range to work within? i.e. you're not hard coding the cell ranges and the formula will cope with random new lower tier vendors without having to re-edit the original formula?
Adam
-
Kevin,
If the [Vendor Name] column is your Primary column, you can use hierarchies to indent the Lower Tier Vendor rows under it.
Then the formula in Contract Closed for the parent (Fake Company 1) row would be
=IF(COUNTIF(CHILDREN(), "Yes") = COUNT(CHILDREN()), "Yes", "No")
Hope this helps.
Craig
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives