Apply conditional formatting with formula (Need to populate a cell green only if it falls in range o

Aayushi Gupta

Dear Team ,

 

Need an urgent help . I want to start using the Conditional formatting on only one cell now the complete row . If you see snapshot below , I want to make highlighted cell (Column Jan and Row 3 i.e. 3Jan) to get green if the value is between (Jan2-(10%Jan2), Jan2+(10%Jan2)). Could you please help on this on top priority. Also pls note I want to only make single cell go green i.e. Jan3 not complete row. I tried many videos but could not find any answer.

 

 

Regards,

Aayushi

Andrée Starå

Hi @Aayushi Gupta

Can you add the screenshot again?

I hope that helps!

Happy Holidays and Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Aayushi Gupta

smartsheet1.docx

Genevieve P.

Hi @Aayushi Gupta

Conditional Formatting rules won't be able to reference the cell above and determine a portion of a value to identify if a cell should turn a certain colour in this way.

Based on your sheet set-up, it may not be possible to get exactly what you're looking for. However, you could potentially add in helper columns with formulas in order to indicate if the cell below meets your criteria (is between a specific value).

You would need to add in one helper column per-month, so it could make your sheet quite big depending on how many months you have. Keep in mind that you can hide all of these columns after the initial set-up.

Example

The way this would work is that the helper column would check a box if the cell in this current row is between the value range you're looking for. Then the Conditional Formatting rule will look at the Helper column instead of your month column. Does that make sense?

Here is my example sheet:

Formula

Notice how the Helper Column 1 has some checkboxes checked? The formula I used in row three is as follows:

=IF(AND(Year@row = "FY'21", Jan@row >= (Jan2 * 0.9), Jan@row <= (Jan2 * 1.1)), 1, 0)

This first checks to see if the current row is an FY'21 row (ignoring the formula if the row is an FY'20 row). Then it checks to see if the data in January's column in this row (using @row) is Greater Than the row above (row 2, or Jan2) - 10%, or is Less Than the row above + 10%.

If you drag-fill the formula down the column it will update the references to look at the row above... so if you drag it down to the next cell, it will check row 3 for the value instead of row 2:

=IF(AND(Year@row = "FY'21", Jan@row >= (Jan3 * 0.9), Jan@row <= (Jan3 * 1.1)), 1, 0)

This is why I asked it to check and see if the current row is FY'21, since you won't want rows that have FY'20 looking into the FY'21 row above.

Conditional Formatting

Then I've set up a Conditional Formatting rule to look at this helper column, and if it's checked, turn just the January Column green:

Extra Columns, Formulas, and Rules

Finally, you would need to adjust the formula for each Helper Column, so for February's checkbox it would be:

=IF(AND(Year@row = "FY'21", Feb@row >= (Feb2 * 0.9), Feb@row <= (Feb2 * 1.1)), 1, 0)

Then you would need to create an individual Conditional Formatting rule for each of these helper columns.

As far as I can see, this would be the only way to achieve your goal with your current set up, but if anyone else has a different idea (@Andrée Starå?) then I'd be very interested to see!

I hope this helps,

Cheers,

Genevieve