Forum Discussion
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.
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
- Hi there,
Have you attached file?- pawelj795Copper Contributor
- Riny_van_EekelenPlatinum Contributor
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.