If/Then Statement

Kevin Snyder

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.

Rob Hagan

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.

15UZU

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

J. Craig Williams

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