Formula That Checks Multiple Rows For Filled Cells
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?
Help Article Resources
Check out the Formula Handbook template!