Conditional Formatting with Relative Reference

Question

Hi There,

I am looking to apply a relative reference to a color scale conditional formatting.

My use case is shown below:

I would like the stock coil weight to change color relative to the initial coil weight in the same row.

ex. P93 would be colored RED relative to the number shown in I93 (showing the stock weight is low). and P98 would be YELLOW relative to I98 (showing we are halfway through the stock).

I am able to create the conditional formation I need for a single cell. Although, for the rest of the 100s+ cells I would have to create a seperate condition for each. Is there anyway to apply the same formatting to all the other P column cells.

Thanks,

Answer 1

Re: complicated conditional formatting

It seems it would be less complicated and easier to maintain by

adjusting the Status column.

For instance...

< 50% = Partial

< 25% = Low or < Reorder point = Low

Then conditional format the status column using the description.

'---

Nothing Left to Lose https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU (free excel programs)

Answer 2

Assuming that the data begin in row 2: select the range from P2 to the last used row in column P.

P2 should be the active cell in the selection,

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Format only cells that contain'.

Leave the first drop down set to 'Cell Value'.

Select 'less than' from the second drop down.

Enter the following formula in the box next to it:

=I2*25%

Note that the cell reference I2 does not contain $ signs.

Click Format...

Activate the Fill tab.

Select red.

Click OK, then click OK again.

This will color cells red if their value is less than 25% of that in column I.

Create other rules as needed.

You may have to change the order of the rules in Conditional Formatting > Manage Rules... to get the effect you want.

Answer 3

Worked Great!

Thanks,