Formula That Checks Multiple Rows For Filled Cells

Benjamin O'Leary

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

Paul Newcome

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.

Paul Newcome

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.

Benjamin O'Leary

@Paul Newcome Thank you for the suggestion. The formula worked perfectly! I do have an additional question. I set up the conditional formatting based on the box being checked:

It should make all rows where there is a check mark green. However, nothing happens:

Am I missing something?

Benjamin O'Leary

@Paul Newcome disregard my conditional formatting question. I managed to figure it out. Thanks again!

Paul Newcome

Glad you got it figured. When looking a the conditional formatting from your screenshot, the text represents the cell, and the bar to the right is the bar that appears in Gantt view.