Forum Discussion

pawelj795's avatar
pawelj795
Copper Contributor
Feb 19, 2021
Solved

Sorting in conditional formatting based on the other column

Hi,
I have a problem with sorting data in my model.
On the first sheet, I have a price list with my products main prices and their latest variances.
As you can see on the attached file, these variances could be positive (green color) and negative (red color).
On the second page, I want to vlookup these variances, but as absolute values.
However, these absolute values must be conditional formatted by values from the first page.

I created rules which are working when I didn't sort my values from largest to smallest and vice versa.

When I sort them, everything is broking.

I simplified my model as much as I can.

 

 

 

  • pawelj795 

    Change the CF rules in the sorted sheet to:

     

    Green: =VLOOKUP(A2,Sheet1!$A$2:$D$9,4,0)>0.02

    Red: =VLOOKUP(A2,Sheet1!$A$2:$D$9,4,0)<-0.02

     

    Then it will work as you intend. 

3 Replies

Resources