Advanced Conditional Formatting Question

APlatipodis
APlatipodis ✭✭
edited 11/15/22 in Smartsheet Basics

Hi Smartsheet Community! Does anyone know if Smartsheet has the capabilities to apply a conditional formatting rule, but the rule will grey out other rows based on the selection you made in the row where the rule is applied? For example, I'm working on an onboarding checklist at my company, and want to apply a rule to check a box in one row and based on if that box is checked or marked it would grey out a training section further down in the onboarding checklist. Is this at all possible or can you only apply rules to rows where the rule is either met or not met?

Thanks in advance!

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @APlatipodis

    Personally the way I would do this is to set up hierarchy so I know what rows are associated with other rows. So your 10 - 15 rows would be indented under this top-level row that dictates the X appearing or not.

    Then we can use the PARENT function to check if the box is checked, or if a cell says "X", or whatever your condition is, then apply this to the Child Row in the a helper column.

    For example, if I'm looking for a checked box, I could use a formula like this:

    =IF(PARENT(Done@row) = 1, "X")

    Or in your case it could look for an "X":

    =IF(PARENT(Done@row) = "X", "X")

    Then I can set that formula to be a Column Formula, and hide the column in the sheet. Would this structure work for you?

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Hi @APlatipodis

    Conditional Formatting will only apply to the row where the condition is met. This means if you check a box higher up in the sheet, a lower row without a box checked will not have the formatting applied.

    However! We could likely use a Formula to automate checking a box or updating a criteria on a different row, based on your current row. Then you can set the Formatting based on that formula column - does that make sense?

    If you need help with this, it would be useful to see a screen capture of your sheet and an explanation of what rows should be updated, but please block out sensitive data.

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Hi @Genevieve P.,

    Thank you for your response and insight on this question. Due to PHI concerns, I can't provide a screenshot of the sheet I'm working in.

    Ultimately, do you know if there is a formula automation where I could select an 'X' from the drop down in column 1 row 1 of my sheet and if that 'X' is selected it would hide or apply conditional formatting to a cluster of rows below (let's just say rows 15-20)?

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @APlatipodis

    Personally the way I would do this is to set up hierarchy so I know what rows are associated with other rows. So your 10 - 15 rows would be indented under this top-level row that dictates the X appearing or not.

    Then we can use the PARENT function to check if the box is checked, or if a cell says "X", or whatever your condition is, then apply this to the Child Row in the a helper column.

    For example, if I'm looking for a checked box, I could use a formula like this:

    =IF(PARENT(Done@row) = 1, "X")

    Or in your case it could look for an "X":

    =IF(PARENT(Done@row) = "X", "X")

    Then I can set that formula to be a Column Formula, and hide the column in the sheet. Would this structure work for you?

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions