Advanced Conditional Formatting Question
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
-
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)?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives