Formula That Checks Multiple Rows For Filled Cells

Options

Hello,

I currently have a Smartsheet that contains two different CR Numbers; each CR number populates multiple rows (see screenshot below).

I am trying to set up a formula that will go into the Eff Date column. If a user were to populate the Eff Date cell for one of the rows (for example, row 1), the formula will check all of the rows with matching CR Numbers (for this example, it would check all rows with a CR Number of 123). If the matching CR Number rows don't have Eff Date populated, then nothing happens. However, once all Eff Date fields are populated for all matching CR Numbers, then all of the rows with that particular CR Number will be highlighted green.

I know there's a way to do this via conditional formatting, but I don't know how (or if there is a way) to lump a group of rows together based on CR Number. Any advice on how to go about accomplishing this?

Thank You,

Ben

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    You will need a helper column with a formula. The below would be for a checkbox column that automatically checks the box when that group of rows is ready to be highlighted.


    =IF(COUNTIFS([Eff Date]:[Eff Date], @cell = "", CR:CR, @cell = CR@row) = 0, 1)


    Basically it says that if the count of rows that have a blank [Eff Date] and a matching CR is zero, then check the box(es).


    Setting up your conditional formatting based on this box being checked should do the trick for you.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!