Conditional Formatting or Formula?

CcHPal

s it possible to create a formula or a conditional formatting rule that would automatically highlight any child rows as they are created and/or based on a specific term or text in a column of the parent row?

Shaine Greenwood

Hello—

You can create an IF statement with the PARENT function that'll allow you to compare your child to the parent and return a value. You can use this value in a conditional formatting rule to change the cell or row when it's present in that column. For example, placing this in a checkbox column of a child row:

=IF(PARENT() = 1, 1)

...will check a box in the child when the checkbox in the parent is checked. More information on creating formulas can be found in our Help Center: https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet

Dan Davis

Yes, but ....  I believe conditional formatting applies to all rows. 

I have used this in a couple ways on a financial sheet regarding Purchase Order entries.  I wanted to flag entries that my staff needs to come back and enter the PO date and the PO number after the PO's are issued. In this case the parent rows would not have dates or PO's entered so I have to consider how to deal with blank cells in these rows.

1. For transaction date entries I have applied conditional formatting that is set up as; if the Transaction Date (a cell in the Transaction Date column) is not blank and is not a date, the formatting is to highlight (pick a color) the entire row.  I rely on my staff to enter the word "pending" (or any other text) in the date cell when they add this transaction to the sheet.  When the PO is issued to the vendor they would come back to enter the date to turn the highlight off.  At a quick glance I can see where dates have not been entered yet.

2.  Similar for PO numbers, if the PO number (a cell in the PO Number column) is not blank and is not a number, the formatting is to highlight (pick a color) the entire row.  Staff would enter "pending" initially then come back to enter the PO number when it is assigned to turn the highlighting off.

For a column where every cell in the column (each row) should have an entry, I have used this conditional formatting that applies to just one column;

If (pick a column name) is blank (a cell in that column is blank) then formatting is (pick a highlight color, I used red), and apply it to the same column name.  If a cell in that column is blank it turns red.  When something is entered in the cell in that column the red highlight on that cell is turned off.  This has helped immensely in looking over a large sheet for missing info.

I have also used this type of strategy to change numbers that are less than zero to be red numbers.

Hope this helps,

Dan Davis