Formatting trouble

Elise McCoppen

If you have created conditional formatting to color alternating rows a different colors, how do you format the document to automatically adjust colors when a new row is added?

J. Craig Williams

Elise,

 

What is your conditional formatting criteria?

Can you post a screen shot?

 

Craig

Etienne Desbiens

Hi,

 

First of all, did you really use conditionnal formatting or did you simply color the lines using the background color function?

In both cases, you can't format the sheet to automatically to adjust colors when a new row is added.

When you create a new row, smartsheet looks at the two rows above and the two rows below to see if there is a repetition of formatting. If there is one, the created row will get the same format.

 

If it can help, I suggest you to copy and paste the rows when creating new ones to keep the colors you had.

 

Hope that helps,

Étienne Desbiens

Elise McCoppen

Hello. 

 

Yes. Conditional formatting is used. Unfortunately, as it is a private account for an organization I cannot use a screen shot; however, the process is as such:

 

1. Add in a new column, named "color"

2. Add a zero (0) in the first row

3. Add the formula, =IF(Color1 = 0, 1, 0) in the second row. 

4. Sequentially, add 0, 1, 0, etc. to the remaining rows in the column. 

5. Select the entire column and choose "conditional formatting"

6. "If (Color is 1) then apply (gray highlight) to to the entire row"

7. Choose a second rule as such, "If (Color is 0) then apply (white highlight) to the entire row 

8. Hide the "Color" column

 

Etienne Desbiens

Well, in that case, when you create a new row, since the formula is always the same, you only have to add a line below. The formula will automatically be added to your new row.

 

To make sure it's added, you only have to highlight a cell and press enter to select the cell right under. The formula will follow.

 

Hope that helps,

Étienne Desbiens

Elise McCoppen

Hello Etienne, 

 

We use our Smart Sheet as a calendar; therefore, sometimes we need to add rows above and/or below in the middle of the sheet. When doing so, the formula does not follow. Should I add a row at the end of the content, yes, the formula does follow, but not in the middle of the sheet.

 

Elise

Etienne Desbiens

I tried many ways but I can't get it to work in the middle of the sheet unless you create a few empty rows before what you would normally do at the end of the sheet...

I suggest you to copy and paste an existing row to the new one to keep the formatting.

 

Étienne

J. Craig Williams

For cases such as these, your best bet may be to copy the formula from cell 2 to the end of the column each time a row is added.

(Limited testing is not auto-filling the formula. I used a checkbox column for the color and this formula: =IF(color94, 0, 1)

 

Copy-pasting a row does not work either as I end up with two instances of the same formula (that is, both pointing to row 94)

 

I vote for a column formula that does not need to be refreshed by the end-user but always apples to each cell in the column.

 

Craig

Etienne Desbiens

Copy-pasting does work... I'm really certain about this one. I tried it many times and it works correctly, the fomula follows and the reference changes.

J. Craig Williams

Etienne, 

 

Are you copying the whole row?

If I click on row 4 (on the row number shown on the far left) and type CTRL+C

and then CTRL+V, the new row is added above (it becomes the new row 4)

 

This formula:

 

=IF(color3, 0, 1)

 

now appears in both row 4 and row 5.

 

The new row 6's formula is 

 

=IF(color5, 0, 1)

 

 

and was before the copy-paste.

Row 5 (the old row 4) has maintained its reference to row 3.

 

Craig

 

Etienne Desbiens

Weird... I test it and it doesn't work anymore... I don't understand why....

 

I was taking screenshots to show you how I made it but I wasn't able to reproduce it.

 

Hum, I'll try to figure out why.

 

Étienne Desbiens

J. Craig Williams

I'm moving to video soon. 

Screenshots do not adequately express some of the weird things I'm seeing.

 

Craig