Is it possible to use conditional formatting on every other row?
You know, like in excel, it is helpful to highlight every other row.
Can that be done in smartsheet?
thx,
Laura
Best Answers
-
Hey @laura.buchanan
To my knowledge, the short answer is no. Here's a possible work-around, if you aren't deleting or moving rows around.
You'll need a helper checkbox column to use in the conditional formatting. You'll also need the system generated autonumber (ie Row ID).
=IF(ISODD([Row ID]@row), 1)
There is also an ISEVEN if that is your preference.
-
Hi @laura.buchanan,
Here's a method that will allow you to delete/move/sort rows at will and still maintain the every other row formatting:
Create an 'Auto-Number/System...' column and choose Auto-Number. In this case, I named the column "X."
Create a new Checkbox column and enter this as a column formula:
=IF(ISEVEN(MATCH(X@row, X:X)), 1)
Now just setup conditional formatting to shade any columns where the box is checked and you should be all set.
-
Thanks so much!
-
Not answered---=IF(ISEVEN(MATCH(X@row, X:X)), 1) gives #CIRCULAR REFERENCE
-
Sadly that's not the behavior I'm getting. If I start with a sheet of 5 rows, the auto-number/system column numbers 1-5. If I delete row 4, the auto-number/system column now shows 1,2,3,5 and refreshing doesn't reorder it. Here's how I have the auto-number column defined. I've tried with and without specifying "1" as the starting number.
Answers
-
Hey @laura.buchanan
To my knowledge, the short answer is no. Here's a possible work-around, if you aren't deleting or moving rows around.
You'll need a helper checkbox column to use in the conditional formatting. You'll also need the system generated autonumber (ie Row ID).
=IF(ISODD([Row ID]@row), 1)
There is also an ISEVEN if that is your preference.
-
Hi @laura.buchanan,
Here's a method that will allow you to delete/move/sort rows at will and still maintain the every other row formatting:
Create an 'Auto-Number/System...' column and choose Auto-Number. In this case, I named the column "X."
Create a new Checkbox column and enter this as a column formula:
=IF(ISEVEN(MATCH(X@row, X:X)), 1)
Now just setup conditional formatting to shade any columns where the box is checked and you should be all set.
-
Thanks so much!
-
Hi there - I'm attempting this method and when I sort it throws off the conditional formatting. I'm sure I'm missing something, any help would be appreciated. I tried locking and freezing the new columns but that didn't work.
-
Not answered---=IF(ISEVEN(MATCH(X@row, X:X)), 1) gives #CIRCULAR REFERENCE
-
Hi @Sue Hill
You'll want to ensure this formula is in a separate column to the one you're referencing. The "X" column should be an auto-number column, and then there is a new column to house the formula, like so:
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
I did perform this, working....figured it out later that day.
-
@John Pudar thank you for sharing this. =IF(ISEVEN(MATCH(X@row, X:X)), 1) worked for me just like you said.
However if I delete a row from my sheet somewhere in the middle the auto-numbered list doesn't reorder itself automatically to close the gap from the now missing number. Is there any way to regenerate the list easily without deleting the column(s) and recreating them any time I have to delete a row? thanks!
-
Hi @Samscho - if you make sure that the "X" column is an 'Auto-Number/System...' column then you should be fine. You may need to refresh the sheet after a change to get the auto-numbering to update, which in turn will update the conditional formatting. But as long as the column you're referencing in the MATCH function is an Auto-Number/System column it should work for you.
-
Sadly that's not the behavior I'm getting. If I start with a sheet of 5 rows, the auto-number/system column numbers 1-5. If I delete row 4, the auto-number/system column now shows 1,2,3,5 and refreshing doesn't reorder it. Here's how I have the auto-number column defined. I've tried with and without specifying "1" as the starting number.
Help Article Resources
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
Check out the Formula Handbook template!