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

Options
Kevin Snyder
edited 12/09/19 in Archived 2016 Posts

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.

Screen.PNG

Comments

  • Rob Hagan
    Rob Hagan ✭✭✭
    Options

    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
    15UZU ✭✭
    Options

    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
    J. Craig Williams ✭✭✭✭✭✭
    Options

    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

This discussion has been closed.