Conditional formatting using forms

Kyle Keever

Hi SS Community,

I have a review sheet that is populated with a corresponding web form. I would like to have conditional formatting highlight the difference in rating. So in the case of the screen shot below where Amanda is giving herself a 3 and Julie is scoring her at 1 the two cells would be red. If they were off by "1" rather than "2" it would be yellow and if they were equal it would be green. Ultimately I would like to have multiple pairs of reviews in one sheet but I am not opposed to having a separate sheet for each pair of reviews if that's what I need to accomplish this.

Thanks for any guidance.

Kyle

Ray Lindstrom

Hi @Kyle Keever,

Which column would be used to indicate the relationship between the two rows? Need to know this criteria in order to build an IF statement.

All the best,

-Ray

Ray Lindstrom

Hi @Kyle Keever,

You would need a column that ties the pairs together if you're going to have one file with multiple pairs.

With a separate file per pair, you could use the following formula on line 1 to find the difference. You would need a column titled something like "Difference" in order to insert the formula and hold the value.

=IF([Synapse Dojo]@row > [Synapse Dojo]2, [Synapse Dojo]@row - [Synapse Dojo]2, IF([Synapse Dojo]2 > [Synapse Dojo]@row, [Synapse Dojo]2 - [Synapse Dojo]@row, 0))

For row 2 you could use this reference in the cell "=Difference1" which will copy the value from line 1 to line 2.

Then you could set your conditional formatting based on the difference column value, and set your colors accordingly to format the Synapse Dojo column.

Hope this helps!

BRgds,

-Ray

Ray Lindstrom

@Kyle Keever,

The more I think about it, on separate sheet, the row 1 formula for the Difference column could be simplified to this:

=ABS([Synapse Dojo]@row - [Synapse Dojo]2)

Row 2's difference column would still be:

=Difference1

Kyle Keever

Thanks for the help @Ray Lindstrom . I actually had something similar set up but since the sheet is being populated by a web form this kind of setup means going in after the web form is filled out and applying the formulas and with that I might as well abandon conditional cell formatting in favor of manual. Can you think of a way this could work with the data coming in as a new row from the web form?

thanks again!

Kyle

Ray Lindstrom

@Kyle Keever,

I don't know of any way you can apply the formula when rows are added. I wish Automation for the "Change cell value" would allow you to enter formulas into cells, but it doesn't. Any value you add to this automation type is entered into the cell as a string, so the formula would display in the cell but it would not function.

My recommendation was for if you were to use a different grid for each pair. If you did that, you could "Save as new" on the grid for each instance of pairs, and your formula would already be in place.

Good luck on your quest!

All the best,

-Ray